﻿using EPlatform_web.App_Start;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Hosting;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Configuration;

namespace EPlatform_web.Controllers
{
    public class KpiController : Controller
    {
        #region 變量、數據源
        /// <summary>
        /// 域，[web.config]
        /// </summary>
        private static readonly string Contract = ConfigurationManager.AppSettings["Contract"];

        /// <summary>
        /// 部門
        /// </summary>
        public DataTable GetDeptDt()
        {
            try
            {
                string deptSql = @"select t.dept_id,
       t.dept_no,
       t.dept_nm,
       t.loc,
       t.dept_no || '-' || t.dept_nm dept_desc
  from IFSAPP.ETMD_DEPT_TAB t
 order by t.dept_no";
                DataSet dataSet = OracleOperation.QueryData(deptSql);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 週期類別
        /// </summary>
        public DataTable GetPeriodType()
        {
            try
            {
                string querySql = @"select * from IFSAPP.ETMD_PERIOD_V t order by t.conf_no";
                DataSet dataSet = OracleOperation.QueryData(querySql);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 衡量頻率
        /// </summary>
        public DataTable GetFreq()
        {
            try
            {
                string querySql = @"select * from IFSAPP.ETMD_FREQ_V t order by DECODE(t.conf_nm, '每月', 1, '每季', 2, '每半年', 3, '每年', 4)";
                DataSet dataSet = OracleOperation.QueryData(querySql);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 輸入方式
        /// </summary>
        public DataTable GetInput()
        {
            try
            {
                string querySql = @"select * from IFSAPP.ETMD_INPUT_V t order by t.conf_no";
                DataSet dataSet = OracleOperation.QueryData(querySql);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 判定
        /// </summary>
        public DataTable GetResult()
        {
            try
            {
                string querySql = @"select * from IFSAPP.ETMD_RESULT_V t order by t.conf_no";
                DataSet dataSet = OracleOperation.QueryData(querySql);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 數值趨勢
        /// </summary>
        public DataTable GetTrend()
        {
            try
            {
                string querySql = @"select * from IFSAPP.ETMD_TREND_V t order by t.conf_no";
                DataSet dataSet = OracleOperation.QueryData(querySql);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 年度
        /// </summary>
        public DataTable GetYearly()
        {
            try
            {
                string querySql = @"select * from IFSAPP.ETMD_YEARLY_T_V t order by t.conf_no";
                DataSet dataSet = OracleOperation.QueryData(querySql);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 單位
        /// </summary>
        public DataTable GetUom()
        {
            try
            {
                string querySql = @"select * from IFSAPP.ETMD_UOM_V t order by t.conf_no";
                DataSet dataSet = OracleOperation.QueryData(querySql);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 數值類型
        /// </summary>
        public DataTable GetVForm()
        {
            try
            {
                string querySql = @"select * from IFSAPP.ETMD_V_FORM_V t order by t.conf_no";
                DataSet dataSet = OracleOperation.QueryData(querySql);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 判斷符號
        /// </summary>
        public DataTable GetOper()
        {
            try
            {
                string querySql = @"select * from IFSAPP.ETMD_OPER_V t order by t.conf_no";
                DataSet dataSet = OracleOperation.QueryData(querySql);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region KPI目標
        /// <summary>
        /// kpi目標View
        /// </summary>
        public ActionResult KpiMain()
        {
            if (!Hpubilc.UserInvalid(Session["Card_No"]) || !Hpubilc.UserInvalid(Session["Dept"]) || !Hpubilc.UserInvalid(Session["PERMISSIONS"]))
            {
                return Content("<script>alert('登錄已過期，請重新登錄！');window.location.href='/Login';</script>");
            }
            ViewBag.PageDesc = "KPI";
            ViewBag.Page = Hpubilc.GetPageInfo(Session["Card_No"].ToString(), "Kpi/KpiMain").Tables[0].Rows;
            ViewBag.PageL2 = Hpubilc.GetPageInfoL2(Session["Card_No"].ToString(), "Kpi/KpiMain").Tables[0].Rows;
            ViewBag.Dept = GetDeptDt().Rows;
            ViewBag.KpiData = SearchData("FP", true).Tables[0];
            ViewBag.Contract = Contract;
            return View();
        }
        /// <summary>
        /// KpiDataView
        /// </summary>
        public ActionResult BtnSearchClick()
        {
            if (!Hpubilc.UserInvalid(Session["Card_No"]) || !Hpubilc.UserInvalid(Session["Dept"]) || !Hpubilc.UserInvalid(Session["PERMISSIONS"]))
            {
                return Content("<script>alert('登錄已過期，請重新登錄！');window.location.href='/Login';</script>");
            }
            string flipPage = Request["flipPage"];
            ViewBag.KpiData = SearchData(flipPage).Tables[0];
            return PartialView("KpiData");
        }
        /// <summary>
        /// 查詢
        /// </summary>
        private DataSet SearchData(string flipPage, bool firstQuery = false)
        {
            string querySql = @"SELECT T.KPI_ID,
       T.SITE,
       T.KPI_YEAR,
       T.KPI_NO,
       T.KPI_DESC,
       T.KPI_OWNER,
       T.KPI_TARGET,
       T.KPI_VALUE,
       T1.KPI_VALUE PRE_KPI_VALUE,
       CASE
         WHEN T.TREND_ID = 58 AND T.KPI_VALUE = T1.KPI_VALUE THEN
          CHR(41304)
         WHEN T.TREND_ID = 58 AND T.KPI_VALUE > T1.KPI_VALUE THEN
          CHR(41460)
         WHEN T.TREND_ID = 58 AND T.KPI_VALUE < T1.KPI_VALUE THEN
          CHR(41461)
         WHEN T.TREND_ID = 57 AND T.KPI_VALUE = T1.KPI_VALUE THEN
          CHR(41304)
         WHEN T.TREND_ID = 57 AND T.KPI_VALUE > T1.KPI_VALUE THEN
          CHR(41461)
         WHEN T.TREND_ID = 57 AND T.KPI_VALUE < T1.KPI_VALUE THEN
          CHR(41460)
       END VALUE_TREND,
       T.KPI_FUNC,
       T.FREQ_DESC,
       T.V_FORM_DESC,
       T.TREND_DESC,
       T.INPUT_TYPE,
       T.UOM_NM,
       T.YEARLY_TYPE,
       T.OWNER_ID,
       T.FREQ_ID,
       T.V_FORM_ID,
       T.TREND_ID,
       T.INPUT_ID,
       T.UOM_ID,
       T.YEARLY_TYPE_ID,
       T.STATUS_V,
       T.CREATED_D,
       T.UPDATE_D,
       T.STATUS_E,
       T.IS_LOCK,
       T.VERSION_NO,
       D.DEPT_ID,
       row_number() over(partition by t.site, t.kpi_year, t.kpi_no order by t.version_no desc) rnum
  FROM IFSAPP.ETMD_KPI01_TAB T
  LEFT JOIN IFSAPP.ETMD_KPI01_LAST_V T1
    ON T.SITE = T1.SITE
   AND T.KPI_NO = T1.KPI_NO
   AND T.KPI_YEAR - 1 = T1.KPI_YEAR
  LEFT JOIN IFSAPP.ETMD_USER_TAB U
    ON T.OWNER_ID = U.USER_ID
  LEFT JOIN IFSAPP.ETMD_DEPT_TAB D
    ON U.DEPT_ID = D.DEPT_ID
 WHERE 1 = 1";
            string txtKpiSite = firstQuery ? Contract : Request["txtKpiSite"];
            string txtKpiYear = firstQuery ? DateTime.Now.Year.ToString() : Request["txtKpiYear"];
            string txtKpiNo = Request["txtKpiNo"];
            string txtKpiDesc = Request["txtKpiDesc"];
            string txtKpiTarget = Request["txtKpiTarget"];
            string txtKpiDept = Request["txtKpiDept"];
            string txtKpiVersion = Request["txtKpiVersion"];
            string txtKpiTrend = Request["txtKpiTrend"];
            string TxtKpiStatus = Request["TxtKpiStatus"];
            if (!string.IsNullOrEmpty(txtKpiSite))
            {
                querySql += " AND UPPER(T.SITE) LIKE '%" + txtKpiSite.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiYear))
            {
                querySql += " AND T.KPI_YEAR LIKE '%" + txtKpiYear + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiNo))
            {
                querySql += " AND T.KPI_NO LIKE '" + txtKpiNo + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiDesc))
            {
                querySql += " AND UPPER(T.KPI_DESC) LIKE '%" + txtKpiDesc.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiTarget))
            {
                querySql += " AND UPPER(T.KPI_TARGET) LIKE '%" + txtKpiTarget.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiDept))
            {
                querySql += " AND D.DEPT_ID = '" + txtKpiDept + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiVersion))
            {
                querySql += " AND T.VERSION_NO = '" + txtKpiVersion + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiTrend))
            {
                querySql += @" AND (CASE
         WHEN T.TREND_ID = 58 AND T.KPI_VALUE = T1.KPI_VALUE THEN
          CHR(41304)
         WHEN T.TREND_ID = 58 AND T.KPI_VALUE > T1.KPI_VALUE THEN
          CHR(41460)
         WHEN T.TREND_ID = 58 AND T.KPI_VALUE < T1.KPI_VALUE THEN
          CHR(41461)
         WHEN T.TREND_ID = 57 AND T.KPI_VALUE = T1.KPI_VALUE THEN
          CHR(41304)
         WHEN T.TREND_ID = 57 AND T.KPI_VALUE > T1.KPI_VALUE THEN
          CHR(41461)
         WHEN T.TREND_ID = 57 AND T.KPI_VALUE < T1.KPI_VALUE THEN
          CHR(41460)
       END) = '" + txtKpiTrend + "'";
            }
            if (!string.IsNullOrEmpty(TxtKpiStatus))
            {
                querySql += " AND T.STATUS_E = '" + TxtKpiStatus + "'";
            }
            //權限控制
            if (Session["PERMISSIONS"].ToString() != "1")
            {
                querySql += " AND D.DEPT_NO = '" + Session["Dept"].ToString() + "'";
            }
            querySql += " ORDER BY T.SITE, T.KPI_YEAR, T.KPI_NO, T.VERSION_NO";
            DataSet dataSet = OracleOperation.QueryData(querySql);

            //分頁
            string finSql = @"SELECT *
  FROM (SELECT ROWNUM RN, TD.*
          FROM ({0}) TD) T
 WHERE T.RN BETWEEN {1} AND {2}";
            int pagesIndex = Convert.ToInt32(string.IsNullOrEmpty(Request["pagesIndex"]) ? "1" : Request["pagesIndex"]);
            int pagesTo = Convert.ToInt32(string.IsNullOrEmpty(Request["pagesTo"]) ? pagesIndex.ToString() : Request["pagesTo"]);
            int pagesNum = Convert.ToInt32(string.IsNullOrEmpty(Request["pagesNum"]) ? "10" : Request["pagesNum"]);
            int indexF = 0;
            int indexT = 0;
            int totalNum = dataSet.Tables[0].Rows.Count;
            int maxPages = (int)Math.Ceiling((double)totalNum / (double)pagesNum);

            if (flipPage == "FP")
            {
                //首頁
                pagesIndex = 1;
                indexT = pagesNum;
                indexF = 1;
                finSql = string.Format(finSql, querySql, indexF, indexT);
            }
            else if (flipPage == "PP")
            {
                //上一頁
                pagesIndex = pagesIndex - 1 <= 0 ? 1 : pagesIndex - 1;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySql, indexF, indexT);
            }
            else if (flipPage == "NP")
            {
                //下一頁
                pagesIndex = pagesIndex + 1 > maxPages ? maxPages : pagesIndex + 1;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySql, indexF, indexT);
            }
            else if (flipPage == "LP")
            {
                //尾頁
                pagesIndex = maxPages;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySql, indexF, indexT);
            }
            else if (flipPage == "TP")
            {
                //轉頁
                pagesTo = pagesTo > maxPages ? maxPages : pagesTo;
                pagesTo = pagesTo <= 0 ? 1 : pagesTo;
                pagesIndex = pagesTo;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySql, indexF, indexT);
            }
            else if (flipPage == "CP")
            {
                //當前頁
                //pagesIndex = pagesIndex;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySql, indexF, indexT);
            }

            ViewBag.PagesIndex = pagesIndex;
            ViewBag.PagesCount = maxPages;
            ViewBag.DataNums = totalNum;
            ViewBag.pagesNum = pagesNum;
            ViewBag.IsLock = "";
            dataSet = OracleOperation.QueryData(finSql);
            return dataSet;
        }
        /// <summary>
        /// 保存
        /// </summary>
        public string BtnKpiSaveClick()
        {
            try
            {
                //string user = Session["Card_No"].ToString();
                var data = Request["updateData"];
                if (string.IsNullOrEmpty(data))
                {
                    return "{\"flag\":false,\"msg\":\"沒有數據需要保存！\"}";
                }
                string checkSql = @"SELECT 1
  FROM IFSAPP.ETMD_KPI01_TAB T
 WHERE T.KPI_ID = {0}
   AND T.SITE = '{1}'
   AND T.KPI_YEAR = {2}
   AND T.VERSION_NO = {3}
   AND T.IS_LOCK = 'Y'";
                string updateSql = @"UPDATE IFSAPP.ETMD_KPI01_TAB T
   SET T.KPI_VALUE = '{3}', T.STATUS_E = 'Close', T.UPDATE_D = SYSDATE
 WHERE T.KPI_ID = {0}
   AND T.SITE = '{1}'
   AND T.KPI_YEAR = {2}
   AND T.VERSION_NO = {4}";
                string[] dataList = data.Split('&');
                string[] fieldList= dataList[0].Split('^');
                if (!string.IsNullOrEmpty(OracleOperation.GetSelectValue(string.Format(checkSql, fieldList[0], fieldList[1], fieldList[2], fieldList[4]))))
                {
                    return "{\"flag\":false,\"msg\":\"已鎖定，無法修改數據！\"}";
                }
                int index = 0;
                foreach (string item in dataList)
                {
                    fieldList = item.Split('^');
                    index += OracleOperation.Exec(string.Format(updateSql, fieldList[0], fieldList[1], fieldList[2], fieldList[3], fieldList[4]));
                }
                if (index > 0)
                {
                    return "{\"flag\":true,\"msg\":\"保存成功！\"}";
                }
                else
                {
                    return "{\"flag\":false,\"msg\":\"保存失敗！\"}";
                }
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"保存失敗！\"}";
            }
        }
        /// <summary>
        /// 生成下一年度
        /// </summary>
        public string BtnKpiGenClick()
        {
            try
            {
                //string user = Session["Card_No"].ToString();
                var site = Request["site"];
                var year = Request["year"];
                string querySql = "select count(*) from IFSAPP.ETMD_KPI01_TAB t where t.site = '{0}' and t.kpi_year = {1}";
                string dataCount = OracleOperation.GetSelectValue(string.Format(querySql, site, year));
                if (dataCount == "0")
                {
                    return "{\"flag\":false,\"msg\":\"未查詢到當前年度["+ year + "]KPI目標，無法生成下一年度[" + (Convert.ToInt32(year) + 1) + "]KPI目標！\"}";
                }
                else
                {
                    string dataCount2 = OracleOperation.GetSelectValue(string.Format(querySql, site, Convert.ToInt32(year) + 1));
                    if (dataCount2 == "0")
                    {
                        IDataParameter[] parmeterdata = new OracleParameter[2];
                        parmeterdata[0] = new OracleParameter("kpi_year_", OracleDbType.Int32, 80);
                        parmeterdata[0].Value = year;

                        parmeterdata[1] = new OracleParameter("contract_", OracleDbType.Varchar2, 80);
                        parmeterdata[1].Value = Contract;
                        OracleOperation.ExecProcedure("ifsapp.Etmd_Kpi01_api.Gen_Next_Year", parmeterdata);
                        return "{\"flag\":true,\"msg\":\"" + year + "年數據生成成功！\"}";
                    }
                    else
                    {
                        return "{\"flag\":false,\"msg\":\"" + (Convert.ToInt32(year) + 1) + "年數據已存在，無法重複生成！\"}";
                    }
                }
            }
            catch (Exception e)
            {
                return "{\"flag\":false,\"msg\":\"生成失敗！（" + e.Message + "）\"}";
            }
        }
        /// <summary>
        /// 生成月度數據
        /// </summary>
        public string BtnKpiMonthClick()
        {
            try
            {
                string contract = Request["site"];
                string year = Request["year"];
                string checkSql = @"select 1
  from IFSAPP.etmd_kpi02_tab t
  left join etmd_kpi01_last_v e
    on t.kpi_id = e.KPI_ID
 where e.SITE = '{0}'
   and e.KPI_YEAR = {1}";
                string msg = OracleOperation.GetSelectValue(string.Format(checkSql, contract, year));
                if (string.IsNullOrEmpty(msg))
                {
                    IDataParameter[] parmeterdata = new OracleParameter[2];
                    parmeterdata[0] = new OracleParameter("kpi_year_", OracleDbType.Varchar2, 80);
                    parmeterdata[0].Value = year;

                    parmeterdata[1] = new OracleParameter("contract_", OracleDbType.Varchar2, 80);
                    parmeterdata[1].Value = Contract;
                    OracleOperation.ExecProcedure("ifsapp.Etmd_Kpi01_api.Gen_Kpi_MonthData", parmeterdata);
                    return "{\"flag\":true,\"msg\":\"" + year + "年月度實際數據生成成功！\"}";
                }
                else
                {
                    return "{\"flag\":false,\"msg\":\"" + year + "年月度實際數據已存在，無法重複生成！\"}";
                }
            }
            catch(Exception e)
            {
                return "{\"flag\":false,\"msg\":\"生成失敗！（" + e.Message + "）\"}";
            }
        }
        /// <summary>
        /// （流程負責人）關閉/取消
        /// </summary>
        public string KpiCloseOrCancel()
        {
            try
            {
                //string user = Session["Card_No"].ToString();
                var data = Request["data"];
                if (string.IsNullOrEmpty(data))
                {
                    return "{\"flag\":false,\"msg\":\"請選擇至少一條數據！\"}";
                }
                string[] dataList = data.Split('&');
                string checkSql = @"SELECT 1
  FROM IFSAPP.ETMD_KPI01_TAB T
 WHERE T.KPI_ID = {0}
   AND T.SITE = '{1}'
   AND T.KPI_YEAR = {2}
   AND T.VERSION_NO = {3}
   AND T.IS_LOCK = 'Y'";
                string updateSql = @"UPDATE IFSAPP.ETMD_KPI01_TAB T
   SET T.STATUS_E = DECODE(T.STATUS_E, 'Close', 'Release', 'Close')
 WHERE T.KPI_ID = '{0}'
   AND T.SITE = '{1}'
   AND T.KPI_YEAR = {2}
   AND T.VERSION_NO = {3}";
                string[] fieldList = dataList[0].Split('^');
                if (!string.IsNullOrEmpty(OracleOperation.GetSelectValue(string.Format(checkSql, fieldList[0], fieldList[1], fieldList[2], fieldList[3]))))
                {
                    return "{\"flag\":false,\"msg\":\"已鎖定，無法修改數據！\"}";
                }
                foreach (string item in dataList)
                {
                    fieldList = item.Split('^');
                    OracleOperation.Exec(string.Format(updateSql, fieldList[0], fieldList[1], fieldList[2], fieldList[3]));
                }
                return "{\"flag\":true,\"msg\":\"操作成功！\"}";
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"操作失敗！\"}";
            }
        }
        /// <summary>
        /// 是否鎖定span
        /// </summary>
        public string IsLockSpan()
        {
            try
            {
                var site = Request["site"];
                var year = Request["year"];
                string isLock = "";
                if (!string.IsNullOrEmpty(site) && !string.IsNullOrEmpty(year))
                {
                    isLock = OracleOperation.GetSelectValue(@"SELECT DECODE(T.IS_LOCK, 'Y', CHR(42103), CHR(42428)) || CHR(49898) ||
       CHR(43383) IS_LOCK
  FROM IFSAPP.ETMD_KPI01_TAB T
 WHERE T.SITE = '" + site + @"'
   AND T.KPI_YEAR = " + year + @"
 GROUP BY T.IS_LOCK");
                    isLock = string.IsNullOrEmpty(isLock) ? "未鎖定" : isLock;
                    isLock = "【" + isLock + year + "】";
                }
                return "{\"flag\":true,\"isLock\":\"" + isLock + "\"}";
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"發生錯誤！\"}";
            }
        }
        /// <summary>
        /// 是否鎖定
        /// </summary>
        public string IsLock()
        {
            try
            {
                var site = Request["site"];
                var year = Request["year"];
                string checkSql = @"SELECT 1
  FROM IFSAPP.ETMD_KPI01_TAB T
 WHERE T.SITE = '{0}'
   AND T.KPI_YEAR = {1}
   AND (T.KPI_VALUE IS NULL OR T.STATUS_E = 'Release')";
                if (!string.IsNullOrEmpty(OracleOperation.GetSelectValue(string.Format(checkSql, site, year))))
                {
                    return "{\"flag\":false,\"msg\":\"[" + year + "]負責人未全部確認，無法鎖定！\"}";
                }
                string querySql = @"SELECT COUNT(*)
  FROM IFSAPP.ETMD_KPI01_TAB T
 WHERE T.SITE = '{0}'
   AND T.KPI_YEAR = {1}
   AND T.IS_LOCK = 'Y'";
                string dataCount = OracleOperation.GetSelectValue(string.Format(querySql, site, year));
                if (dataCount == "0")
                {
                    return "{\"flag\":true,\"isLock\":false}";
                }
                else
                {
                    return "{\"flag\":true,\"isLock\":true}";
                }
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"操作失敗！\"}";
            }
        }
        /// <summary>
        /// （QA）鎖定/取消
        /// </summary>
        public string KpiLockOrCancel()
        {
            try
            {
                //string user = Session["Card_No"].ToString();
                var site = Request["site"];
                var year = Request["year"];
                var flag = Request["flag"];
                string updateSql = @"UPDATE IFSAPP.ETMD_KPI01_TAB T
   SET T.IS_LOCK = '{3}'
 WHERE T.SITE = '{0}'
   AND T.KPI_YEAR = {1}
   AND T.IS_LOCK = '{2}'";
                OracleOperation.Exec(string.Format(updateSql, site, year, flag == "lock" ? "Y" : "N", flag == "lock" ? "N" : "Y"));
                return "{\"flag\":true,\"msg\":\"操作成功！\"}";
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"操作失敗！\"}";
            }
        }
        public string BtnKpiVersionClick()
        {
            try
            {
                string updateData = Request["updateData"];
                string[] list = updateData.Split(',');
                if (list.Length > 0)
                {
                    string insertSql = @"insert into IFSAPP.etmd_kpi01_tab
  (kpi_id,
   site,
   kpi_year,
   kpi_no,
   kpi_desc,
   kpi_owner,
   kpi_target,
   kpi_value,
   kpi_func,
   freq_desc,
   v_form_desc,
   trend_desc,
   input_type,
   uom_nm,
   yearly_type,
   owner_id,
   freq_id,
   v_form_id,
   trend_id,
   input_id,
   uom_id,
   yearly_type_id,
   status_v,
   created_d,
   update_d,
   status_e,
   is_lock,
   version_no,
   oper_type,
   oper_id)
  select {1},
         t.site,
         t.kpi_year,
         t.kpi_no,
         t.kpi_desc,
         t.kpi_owner,
         t.kpi_target,
         t.kpi_value,
         t.kpi_func,
         t.freq_desc,
         t.v_form_desc,
         t.trend_desc,
         t.input_type,
         t.uom_nm,
         t.yearly_type,
         t.owner_id,
         t.freq_id,
         t.v_form_id,
         t.trend_id,
         t.input_id,
         t.uom_id,
         t.yearly_type_id,
         0,
         sysdate,
         null,
         'Release',
         'N',
         t.version_no + 1,
         t.oper_type,
         t.oper_id
    from IFSAPP.etmd_kpi01_tab t
   where t.kpi_id = {0}";
                    int kpiId = Convert.ToInt32(OracleOperation.GetSelectValue("select max(kpi_id) from IFSAPP.etmd_kpi01_tab"));
                    foreach (string item in list)
                    {
                        kpiId++;
                        OracleOperation.Exec(string.Format(insertSql, item, kpiId));
                    }
                    return "{\"flag\":true,\"msg\":\"操作成功！\"}";
                }
                else
                {
                    return "{\"flag\":false,\"msg\":\"請選擇一條數據！\"}";
                }
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"操作失敗！\"}";
            }
        }
        #endregion

        #region KPI實際
        /// <summary>
        /// kpi實際View
        /// </summary>
        public ActionResult KpiAct()
        {
            if (!Hpubilc.UserInvalid(Session["Card_No"]) || !Hpubilc.UserInvalid(Session["Dept"]) || !Hpubilc.UserInvalid(Session["PERMISSIONS"]))
            {
                return Content("<script>alert('登錄已過期，請重新登錄！');window.location.href='/Login';</script>");
            }
            ViewBag.PageDesc = "KPI";
            ViewBag.Page = Hpubilc.GetPageInfo(Session["Card_No"].ToString(), "Kpi/KpiMain").Tables[0].Rows;
            ViewBag.PageL2 = Hpubilc.GetPageInfoL2(Session["Card_No"].ToString(), "Kpi/KpiMain").Tables[0].Rows;
            ViewBag.Dept = GetDeptDt().Rows;
            ViewBag.Period = GetPeriodType().Rows;
            ViewBag.Freq = GetFreq().Rows;
            ViewBag.Input = GetInput().Rows;
            ViewBag.Result = GetResult().Rows;
            DataSet dataSet = SearchDataAct("FP", true);
            ViewBag.KpiActData = dataSet.Tables[1];
            ViewBag.KpiActDataGroup = dataSet.Tables[0];
            ViewBag.KpiLastYearDe = dataSet.Tables[2];
            ViewBag.KpiMonthSel = KpiMonthSel();
            ViewBag.EditOth = false;
            ViewBag.Contract = Contract;
            return View();
        }
        /// <summary>
        /// KpiActDataView
        /// </summary>
        public ActionResult BtnSearchClickAct()
        {
            if (!Hpubilc.UserInvalid(Session["Card_No"]) || !Hpubilc.UserInvalid(Session["Dept"]) || !Hpubilc.UserInvalid(Session["PERMISSIONS"]))
            {
                return Content("<script>alert('登錄已過期，請重新登錄！');window.location.href='/Login';</script>");
            }
            string flipPage = Request["flipPage"];
            ViewBag.Month = string.IsNullOrEmpty(Request["txtKpiMonth"]) ? "0" : Request["txtKpiMonth"];
            string vb_month = ViewBag.Month;
            if (vb_month == "0")
            {
                ViewBag.TdWidth = "320%";
                ViewBag.TaWidth = "90px";
                ViewBag.TfCs = "46";
            }
            else
            {
                switch (vb_month.Split(',').Count())
                {
                    case 1: ViewBag.TdWidth = "100%"; ViewBag.TaWidth = "120px"; ViewBag.TfCs = "5"; break;
                    case 2: ViewBag.TdWidth = "150%"; ViewBag.TaWidth = "170px"; ViewBag.TfCs = "9"; break;
                    case 3: ViewBag.TdWidth = "200%"; ViewBag.TaWidth = "170px"; ViewBag.TfCs = "11"; break;
                    case 4: ViewBag.TdWidth = "200%"; ViewBag.TaWidth = "130px"; ViewBag.TfCs = "16"; break;
                    case 5: ViewBag.TdWidth = "220%"; ViewBag.TaWidth = "110px"; ViewBag.TfCs = "22"; break;
                    case 6: ViewBag.TdWidth = "240%"; ViewBag.TaWidth = "100px"; ViewBag.TfCs = "28"; break;
                    case 7: ViewBag.TdWidth = "260%"; ViewBag.TaWidth = "90px"; ViewBag.TfCs = "30"; break;
                    case 8: ViewBag.TdWidth = "280%"; ViewBag.TaWidth = "90px"; ViewBag.TfCs = "36"; break;
                    case 9: ViewBag.TdWidth = "300%"; ViewBag.TaWidth = "90px"; ViewBag.TfCs = "41"; break;
                    case 10: ViewBag.TdWidth = "320%"; ViewBag.TaWidth = "90px"; ViewBag.TfCs = "46"; break;
                    case 11: ViewBag.TdWidth = "340%"; ViewBag.TaWidth = "90px"; ViewBag.TfCs = "53"; break;
                    case 12: ViewBag.TdWidth = "360%"; ViewBag.TaWidth = "90px"; ViewBag.TfCs = "58"; break;
                }
            }
            DataSet dataSet = SearchDataAct(flipPage);
            ViewBag.KpiActData = dataSet.Tables[1];
            ViewBag.KpiActDataGroup = dataSet.Tables[0];
            ViewBag.KpiLastYearDe= dataSet.Tables[2];
            return PartialView("KpiActData");
        }
        /// <summary>
        /// 查詢
        /// </summary>
        private DataSet SearchDataAct(string flipPage, bool firstQuery = false)
        {
            string querySql = @"SELECT T.KPI_ID,
       T1.SITE,
       T1.KPI_YEAR,
       T1.KPI_NO,
       T1.KPI_DESC,
       T1.KPI_TARGET,
       T1.KPI_FUNC,
       T1.INPUT_TYPE,
       T1.FREQ_DESC,
       T.PERIOD_TYPE,
       T.PERIOD_VALUE,
       T1.UOM_NM,
       T1.TREND_DESC,
       T.KPI_V2,
       regexp_replace(decode(t.kpi_valueact,-1,'NA',to_char(t.kpi_valueact)), '^\.', '0.') KPI_VALUEACT,
       T.STATUS_V,
       T.STATUS_E,
       T.IS_LOCK,
       T.DIFF_ANALYSIS,
       T.IMPROVE_MEASURE,
       TO_CHAR(T.ECD, 'YYYY-MM-DD') ECD,
       T.KPI_RESULT,
       T.REMARK,
       R.CONF_DESC KPI_RESULT_V,
       T1.KPI_OWNER,
       get_result_color(T.KPI_ID,T.PERIOD_VALUE,T.PERIOD_TYPE) RESULT_COLOR
  FROM IFSAPP.ETMD_KPI02_TAB T
  LEFT JOIN IFSAPP.ETMD_KPI01_TAB T1
    ON T.KPI_ID = T1.KPI_ID
  LEFT JOIN IFSAPP.ETMD_USER_TAB U
    ON T1.OWNER_ID = U.USER_ID
  LEFT JOIN IFSAPP.ETMD_DEPT_TAB D
    ON U.DEPT_ID = D.DEPT_ID
  LEFT JOIN IFSAPP.ETMD_RESULT_V R
    ON T.KPI_RESULT = R.CONF_NM
 WHERE 1 = 1";
            string querySqlGroup = @"SELECT T.KPI_ID,
       T1.SITE,
       T1.KPI_YEAR,
       T1.KPI_NO,
       T1.KPI_DESC,
       T1.KPI_TARGET,
       T1.KPI_FUNC,
       T1.INPUT_TYPE,
       T1.FREQ_DESC,
       T.PERIOD_TYPE,
       T1.V_FORM_DESC,
       T1.TREND_DESC,
       T1.YEARLY_TYPE,
       get_yearly_result(t.kpi_id) YEARLY_RESULT,
       SUM(DECODE(T.KPI_RESULT, 2, 1, 0)) UNQTY,
       T1.KPI_OWNER,
       PV.CONF_V2,
       COUNT(*) TOTAL
  FROM IFSAPP.ETMD_KPI02_TAB T
  LEFT JOIN IFSAPP.ETMD_KPI01_TAB T1
    ON T.KPI_ID = T1.KPI_ID
  LEFT JOIN IFSAPP.ETMD_PERIOD_V PV
    ON T.PERIOD_TYPE = PV.CONF_NM
  LEFT JOIN IFSAPP.ETMD_USER_TAB U
    ON T1.OWNER_ID = U.USER_ID
  LEFT JOIN IFSAPP.ETMD_DEPT_TAB D
    ON U.DEPT_ID = D.DEPT_ID
 WHERE 1 = 1";
            string querySqlLastYearDe = querySql;
            string txtKpiSite = firstQuery ? Contract : Request["txtKpiSite"];
            string txtKpiYear = firstQuery ? (DateTime.Now.Month == 1 ? (DateTime.Now.Year - 1).ToString() : DateTime.Now.Year.ToString()) : Request["txtKpiYear"];
            string txtKpiNo = Request["txtKpiNo"];
            string txtKpiDesc = Request["txtKpiDesc"];
            string txtKpiTarget = Request["txtKpiTarget"];
            string txtKpiDept = Request["txtKpiDept"];
            string txtKpiPeriod = string.IsNullOrEmpty(Request["txtKpiPeriod"]) ? "M" : Request["txtKpiPeriod"];
            string txtKpiFreq = Request["txtKpiFreq"];
            string txtKpiInput = Request["txtKpiInput"];
            string txtKpiResult = Request["txtKpiResult"];
            if (!string.IsNullOrEmpty(txtKpiSite))
            {
                querySql += " AND UPPER(T1.SITE) LIKE '%" + txtKpiSite.ToUpper() + "%'";
                querySqlLastYearDe += " AND UPPER(T1.SITE) LIKE '%" + txtKpiSite.ToUpper() + "%'";
                querySqlGroup += " AND UPPER(T1.SITE) LIKE '%" + txtKpiSite.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiYear))
            {
                querySql += " AND T1.KPI_YEAR LIKE '%" + txtKpiYear + "%'";
                querySqlLastYearDe += " AND T1.KPI_YEAR LIKE '%" + (Convert.ToInt32(txtKpiYear) - 1) + "%'";
                querySqlGroup += " AND T1.KPI_YEAR LIKE '%" + txtKpiYear + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiNo))
            {
                querySql += " AND T1.KPI_NO LIKE '" + txtKpiNo + "'";
                querySqlLastYearDe += " AND T1.KPI_NO LIKE '" + txtKpiNo + "'";
                querySqlGroup += " AND T1.KPI_NO LIKE '" + txtKpiNo + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiDesc))
            {
                querySql += " AND UPPER(T1.KPI_DESC) LIKE '%" + txtKpiDesc.ToUpper() + "%'";
                querySqlLastYearDe += " AND UPPER(T1.KPI_DESC) LIKE '%" + txtKpiDesc.ToUpper() + "%'";
                querySqlGroup += " AND UPPER(T1.KPI_DESC) LIKE '%" + txtKpiDesc.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiTarget))
            {
                querySql += " AND UPPER(T1.KPI_TARGET) LIKE '%" + txtKpiTarget.ToUpper() + "%'";
                querySqlLastYearDe += " AND UPPER(T1.KPI_TARGET) LIKE '%" + txtKpiTarget.ToUpper() + "%'";
                querySqlGroup += " AND UPPER(T1.KPI_TARGET) LIKE '%" + txtKpiTarget.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiDept))
            {
                querySql += " AND D.DEPT_ID = '" + txtKpiDept + "'";
                querySqlLastYearDe += " AND D.DEPT_ID = '" + txtKpiDept + "'";
                querySqlGroup += " AND D.DEPT_ID = '" + txtKpiDept + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiPeriod))
            {
                querySql += " AND T.PERIOD_TYPE = '" + txtKpiPeriod + "'";
                querySqlLastYearDe += " AND T.PERIOD_TYPE = '" + txtKpiPeriod + "'";
                querySqlGroup += " AND T.PERIOD_TYPE = '" + txtKpiPeriod + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiFreq))
            {
                querySql += " AND T1.FREQ_ID = '" + txtKpiFreq + "'";
                querySqlLastYearDe += " AND T1.FREQ_ID = '" + txtKpiFreq + "'";
                querySqlGroup += " AND T1.FREQ_ID = '" + txtKpiFreq + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiInput))
            {
                querySql += " AND T1.INPUT_ID = '" + txtKpiInput + "'";
                querySqlLastYearDe += " AND T1.INPUT_ID = '" + txtKpiInput + "'";
                querySqlGroup += " AND T1.INPUT_ID = '" + txtKpiInput + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiResult))
            {
                querySql += " AND Get_CurrMonth_Result(T.KPI_ID) = '" + txtKpiResult + "'";
                querySqlLastYearDe += " AND Get_CurrMonth_Result(T.KPI_ID) = '" + txtKpiResult + "'";
                querySqlGroup += " AND Get_CurrMonth_Result(T.KPI_ID) = '" + txtKpiResult + "'";
            }
            querySqlLastYearDe += " AND (T.PERIOD_VALUE = '11' OR T.PERIOD_VALUE = '12')";
            //權限控制
            if (Session["PERMISSIONS"].ToString() != "1")
            {
                querySql += " AND D.DEPT_NO = '" + Session["Dept"].ToString() + "'";
                querySqlLastYearDe += " AND D.DEPT_NO = '" + Session["Dept"].ToString() + "'";
                querySqlGroup += " AND D.DEPT_NO = '" + Session["Dept"].ToString() + "'";
            }
            querySql += " ORDER BY T1.KPI_NO, DECODE(T.PERIOD_TYPE, 'M', 1, 'Q', 2, 'H', 3, 'Y', 4), T.PERIOD_VALUE";
            querySqlLastYearDe += " ORDER BY T1.KPI_NO, DECODE(T.PERIOD_TYPE, 'M', 1, 'Q', 2, 'H', 3, 'Y', 4), T.PERIOD_VALUE";
            querySqlGroup += @" GROUP BY T.KPI_ID,
          T1.SITE,
          T1.KPI_YEAR,
          T1.KPI_NO,
          T1.KPI_DESC,
          T1.KPI_TARGET,
          T1.KPI_FUNC,
          T1.INPUT_TYPE,
          T1.FREQ_DESC,
          T.PERIOD_TYPE,
          T1.V_FORM_DESC,
          T1.TREND_DESC,
          T1.YEARLY_TYPE,
          T1.YEARLY_TYPE_ID,
          T1.KPI_OWNER,
          PV.CONF_V2
 ORDER BY T1.KPI_NO, DECODE(T.PERIOD_TYPE, 'M', 1, 'Q', 2, 'H', 3, 'Y', 4)";
            DataSet dataSet = OracleOperation.QueryData(querySqlGroup);

            //分頁
            string finSql = @"SELECT *
  FROM (SELECT ROWNUM RN, TD.*
          FROM ({0}) TD) T
 WHERE T.RN BETWEEN {1} AND {2}";
            int pagesIndex = Convert.ToInt32(string.IsNullOrEmpty(Request["pagesIndex"]) ? "1" : Request["pagesIndex"]);
            int pagesTo = Convert.ToInt32(string.IsNullOrEmpty(Request["pagesTo"]) ? pagesIndex.ToString() : Request["pagesTo"]);
            int pagesNum = Convert.ToInt32(string.IsNullOrEmpty(Request["pagesNum"]) ? "10" : Request["pagesNum"]);
            int indexF = 0;
            int indexT = 0;
            int totalNum = dataSet.Tables[0].Rows.Count;
            int maxPages = (int)Math.Ceiling((double)totalNum / (double)pagesNum);

            if (flipPage == "FP")
            {
                //首頁
                pagesIndex = 1;
                indexT = pagesNum;
                indexF = 1;
                finSql = string.Format(finSql, querySqlGroup, indexF, indexT);
            }
            else if (flipPage == "PP")
            {
                //上一頁
                pagesIndex = pagesIndex - 1 <= 0 ? 1 : pagesIndex - 1;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySqlGroup, indexF, indexT);
            }
            else if (flipPage == "NP")
            {
                //下一頁
                pagesIndex = pagesIndex + 1 > maxPages ? maxPages : pagesIndex + 1;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySqlGroup, indexF, indexT);
            }
            else if (flipPage == "LP")
            {
                //尾頁
                pagesIndex = maxPages;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySqlGroup, indexF, indexT);
            }
            else if (flipPage == "TP")
            {
                //轉頁
                pagesTo = pagesTo > maxPages ? maxPages : pagesTo;
                pagesTo = pagesTo <= 0 ? 1 : pagesTo;
                pagesIndex = pagesTo;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySqlGroup, indexF, indexT);
            }
            else if (flipPage == "CP")
            {
                //當前頁
                //pagesIndex = pagesIndex;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySqlGroup, indexF, indexT);
            }

            ViewBag.PagesIndex = pagesIndex;
            ViewBag.PagesCount = maxPages;
            ViewBag.DataNums = totalNum;
            ViewBag.pagesNum = pagesNum;
            ViewBag.IsLock = "";
            DataSet finDataSet = new DataSet();
            dataSet = OracleOperation.QueryData(finSql);
            DataTable dataTable = dataSet.Tables[0];
            dataTable.TableName = "ActGroup";
            finDataSet.Tables.Add(dataTable.Copy());
            dataSet = OracleOperation.QueryData(querySql);
            dataTable = dataSet.Tables[0];
            dataTable.TableName = "Act";
            finDataSet.Tables.Add(dataTable.Copy());
            dataSet = OracleOperation.QueryData(querySqlLastYearDe);
            dataTable = dataSet.Tables[0];
            dataTable.TableName = "December";
            finDataSet.Tables.Add(dataTable.Copy());
            return finDataSet;
        }
        /// <summary>
        /// 保存
        /// </summary>
        public string BtnKpiSaveClickAct()
        {
            try
            {
                string user = Session["Card_No"].ToString();
                var data = Request["updateData"];
                var dataOth = Request["updateDataOth"];
                bool editOthFlag = Convert.ToBoolean(Request["editOthFlag"]);
                if (string.IsNullOrEmpty(data) && string.IsNullOrEmpty(dataOth))
                {
                    return "{\"flag\":false,\"msg\":\"沒有數據需要保存！\"}";
                }
                string checkSql = @"SELECT 1
  FROM IFSAPP.ETMD_KPI02_TAB T
 WHERE T.KPI_ID = {0}
   AND T.PERIOD_TYPE = '{1}'
   AND T.PERIOD_VALUE = '{2}'
   AND T.IS_LOCK = 'Y'";
                string updateSql = @"UPDATE IFSAPP.ETMD_KPI02_TAB T
   SET T.KPI_VALUEACT    = {3},
       T.DIFF_ANALYSIS   = '{4}',
       T.IMPROVE_MEASURE = '{5}',
       T.ECD             = TO_DATE('{6}', 'YYYY/MM/DD'),
       T.REMARK          = '{8}',
       T.KPI_RESULT      = 0,
       T.STATUS_V        = 1,
       T.STATUS_E        = 'Close',
       T.UPDATE_D        = SYSDATE,
       T.UPDATER         = '{7}'
 WHERE T.KPI_ID = {0}
   AND T.PERIOD_TYPE = '{1}'
   AND T.PERIOD_VALUE = '{2}'";

                int index = 0;
                string returnStr = "";
                if (editOthFlag)
                {
                    string[] dataList = dataOth.Split('⊕');
                    string[] fieldList = dataList[0].Split('§');
                    string insertSql = @"INSERT INTO IFSAPP.ETMD_KPI02_HIS_TAB
  SELECT *
    FROM IFSAPP.ETMD_KPI02_TAB T
   WHERE T.KPI_ID = {0}
     AND T.PERIOD_TYPE = '{1}'
     AND T.PERIOD_VALUE = '{2}'";
                    updateSql = @"UPDATE IFSAPP.ETMD_KPI02_TAB T
   SET T.KPI_VALUEACT    = {3},
       T.DIFF_ANALYSIS   = '{4}',
       T.IMPROVE_MEASURE = '{5}',
       T.ECD             = TO_DATE('{6}', 'YYYY/MM/DD'),
       T.REMARK          = '{7}',
       T.KPI_RESULT      = 0,
       T.STATUS_V        = 1,
       T.UPDATE_D        = SYSDATE
 WHERE T.KPI_ID = {0}
   AND T.PERIOD_TYPE = '{1}'
   AND T.PERIOD_VALUE = '{2}'";
                    IDataParameter[] parmeterdata;
                    foreach (string item in dataList)
                    {
                        fieldList = item.Split('§');
                        parmeterdata = new OracleParameter[5];
                        parmeterdata[0] = new OracleParameter("v_pki_id_", OracleDbType.Int32, 80);
                        parmeterdata[0].Value = fieldList[0];
                        parmeterdata[1] = new OracleParameter("v_period_type_", OracleDbType.Varchar2, 80);
                        parmeterdata[1].Value = fieldList[1];
                        parmeterdata[2] = new OracleParameter("v_period_value_", OracleDbType.Varchar2, 80);
                        parmeterdata[2].Value = fieldList[2];
                        parmeterdata[3] = new OracleParameter("v_kpi_valueact_", OracleDbType.Int32, 80);
                        parmeterdata[3].Value = fieldList[3];
                        parmeterdata[4] = new OracleParameter("result_", OracleDbType.Varchar2, 200);
                        parmeterdata[4].Direction = ParameterDirection.Output;
                        returnStr = OracleOperation.ExecProcedureOut("ifsapp.Etmd_Kpi02_api.ETMD_KPI02_CAL_RESULT_OUT", parmeterdata);
                        if (returnStr == "2" && (string.IsNullOrEmpty(fieldList[4]) || string.IsNullOrEmpty(fieldList[5]) || string.IsNullOrEmpty(fieldList[6])))
                        {
                            return "{\"flag\":false,\"msg\":\"判定不通過，請填寫差異分析、改善措施、ECD後重新保存！\"}";
                        }

                        OracleOperation.Exec(string.Format(insertSql, fieldList[0], fieldList[1], fieldList[2]));
                        index += OracleOperation.Exec(string.Format(updateSql, fieldList[0], fieldList[1], fieldList[2], string.IsNullOrEmpty(fieldList[3]) ? "null" : fieldList[3], fieldList[4], fieldList[5], fieldList[6], fieldList[7]));

                        parmeterdata = new OracleParameter[3];
                        parmeterdata[0] = new OracleParameter("v_pki_id_", OracleDbType.Int32, 80);
                        parmeterdata[0].Value = fieldList[0];
                        parmeterdata[1] = new OracleParameter("v_period_type_", OracleDbType.Varchar2, 80);
                        parmeterdata[1].Value = fieldList[1];
                        parmeterdata[2] = new OracleParameter("v_period_value_", OracleDbType.Varchar2, 80);
                        parmeterdata[2].Value = fieldList[2];
                        OracleOperation.ExecProcedure("ifsapp.Etmd_Kpi02_api.ETMD_KPI02_CAL_RESULT_PRO", parmeterdata);
                    }
                }
                else
                {
                    string[] dataList = data.Split('⊕');
                    string[] fieldList = dataList[0].Split('§');
                    if (!string.IsNullOrEmpty(OracleOperation.GetSelectValue(string.Format(checkSql, fieldList[0], fieldList[1], fieldList[2]))))
                    {
                        return "{\"flag\":false,\"msg\":\"已鎖定，無法修改數據！\"}";
                    }
                    //checkSql = @"select 1 from etmd_kpi01_tab t where t.kpi_id = {0} and t.v_form_id=56";
                    //string tmpStr;
                    //decimal tmpInt;
                    //foreach (string item in dataList)
                    //{
                    //    tmpStr = OracleOperation.GetSelectValue(string.Format(checkSql, item.Split('^')[0]), sContract);
                    //    tmpInt = string.IsNullOrEmpty(item.Split('^')[3]) ? 0 : Convert.ToDecimal(item.Split('^')[3]);
                    //    if (!string.IsNullOrEmpty(tmpStr) && tmpInt > 100)
                    //    {
                    //        return "{\"flag\":false,\"msg\":\"單位為%時，數值必須小於等於100！\"}";
                    //    }
                    //}
                    IDataParameter[] parmeterdata;
                    foreach (string item in dataList)
                    {
                        fieldList = item.Split('§');
                        parmeterdata = new OracleParameter[5];
                        parmeterdata[0] = new OracleParameter("v_pki_id_", OracleDbType.Int32, 80);
                        parmeterdata[0].Value = fieldList[0];
                        parmeterdata[1] = new OracleParameter("v_period_type_", OracleDbType.Varchar2, 80);
                        parmeterdata[1].Value = fieldList[1];
                        parmeterdata[2] = new OracleParameter("v_period_value_", OracleDbType.Varchar2, 80);
                        parmeterdata[2].Value = fieldList[2];
                        parmeterdata[3] = new OracleParameter("v_kpi_valueact_", OracleDbType.Int32, 80);
                        parmeterdata[3].Value = fieldList[3];
                        parmeterdata[4] = new OracleParameter("result_", OracleDbType.Varchar2, 200);
                        parmeterdata[4].Direction = ParameterDirection.Output;
                        returnStr = OracleOperation.ExecProcedureOut("ifsapp.Etmd_Kpi02_api.ETMD_KPI02_CAL_RESULT_OUT", parmeterdata);
                        if (returnStr == "2" && (string.IsNullOrEmpty(fieldList[4]) || string.IsNullOrEmpty(fieldList[5]) || string.IsNullOrEmpty(fieldList[6])))
                        {
                            return "{\"flag\":false,\"msg\":\"判定不通過，請填寫差異分析、改善措施、ECD後重新保存！\"}";
                        }

                        index += OracleOperation.Exec(string.Format(updateSql, fieldList[0], fieldList[1], fieldList[2], string.IsNullOrEmpty(fieldList[3]) ? "null" : fieldList[3], fieldList[4], fieldList[5], fieldList[6], user, fieldList[7]));

                        parmeterdata = new OracleParameter[3];
                        parmeterdata[0] = new OracleParameter("v_pki_id_", OracleDbType.Int32, 80);
                        parmeterdata[0].Value = fieldList[0];
                        parmeterdata[1] = new OracleParameter("v_period_type_", OracleDbType.Varchar2, 80);
                        parmeterdata[1].Value = fieldList[1];
                        parmeterdata[2] = new OracleParameter("v_period_value_", OracleDbType.Varchar2, 80);
                        parmeterdata[2].Value = fieldList[2];
                        OracleOperation.ExecProcedure("ifsapp.Etmd_Kpi02_api.ETMD_KPI02_CAL_RESULT_PRO", parmeterdata);
                    }
                }
                if (index > 0)
                {
                    return "{\"flag\":true,\"msg\":\"保存成功！\"}";
                }
                else
                {
                    return "{\"flag\":false,\"msg\":\"保存失敗！\"}";
                }
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"保存失敗！\"}";
            }
        }
        /// <summary>
        /// （流程負責人）關閉/取消
        /// </summary>
        public string KpiCloseOrCancelAct()
        {
            try
            {
                //string user = Session["Card_No"].ToString();
                var data = Request["data"];
                if (string.IsNullOrEmpty(data))
                {
                    return "{\"flag\":false,\"msg\":\"請選擇至少一條數據！\"}";
                }
                string[] dataList = data.Split('&');
                string checkSql = @"SELECT 1
  FROM IFSAPP.ETMD_KPI02_TAB T
 WHERE T.KPI_ID = {0}
   AND T.PERIOD_TYPE = '{1}'
   AND T.PERIOD_VALUE = '{2}'
   AND T.IS_LOCK = 'Y'";
                string updateSql = @"UPDATE IFSAPP.ETMD_KPI02_TAB T
   SET T.STATUS_E   = DECODE(T.STATUS_E, 'Close', 'Release', 'Close'),
       T.STATUS_V   = DECODE(T.STATUS_V, '1', '0', '1'),
       T.KPI_RESULT = DECODE(T.KPI_RESULT, '-1', '0', '-1')
 WHERE T.KPI_ID = {0}
   AND T.PERIOD_TYPE = '{1}'
   AND T.PERIOD_VALUE = '{2}'";
                string[] fieldList = dataList[0].Split('^');
                if (!string.IsNullOrEmpty(OracleOperation.GetSelectValue(string.Format(checkSql, fieldList[0], fieldList[1], fieldList[2]))))
                {
                    return "{\"flag\":false,\"msg\":\"數據已鎖定！\"}";
                }
                foreach (string item in dataList)
                {
                    fieldList = item.Split('^');
                    OracleOperation.Exec(string.Format(updateSql, fieldList[0], fieldList[1], fieldList[2]));
                }
                return "{\"flag\":true,\"msg\":\"操作成功！\"}";
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"操作失敗！\"}";
            }
        }
        /// <summary>
        /// 是否鎖定span
        /// </summary>
        public string IsLockSpanAct()
        {
            try
            {
                var site = Request["site"];
                var year = Request["year"];
                var month = Request["month"];
                string isLock = "";
                if (!string.IsNullOrEmpty(site) && !string.IsNullOrEmpty(year))
                {
                    isLock = OracleOperation.GetSelectValue(@"SELECT DECODE(T.IS_LOCK, 'Y', CHR(42103), CHR(42428)) || CHR(49898) ||
       CHR(43383) IS_LOCK
  FROM IFSAPP.ETMD_KPI02_TAB T
  LEFT JOIN IFSAPP.ETMD_KPI01_TAB T1
    ON T.KPI_ID = T1.KPI_ID
 WHERE T1.SITE = '" + site + @"'
   AND T1.KPI_YEAR = " + year + @"
   AND T.PERIOD_TYPE = 'M'
   AND T.PERIOD_VALUE = '" + month + @"'
 GROUP BY T.IS_LOCK");
                    isLock = string.IsNullOrEmpty(isLock) ? "未鎖定" : isLock;
                    isLock = "【" + isLock + year + month + "】";
                }
                return "{\"flag\":true,\"isLock\":\"" + isLock + "\"}";
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"發生錯誤！\"}";
            }
        }
        /// <summary>
        /// 是否鎖定
        /// </summary>
        public string IsLockAct()
        {
            try
            {
                var site = Request["site"];
                var year = Request["year"];
                var month = Request["month"];
                string checkSql = @"SELECT 1
  FROM IFSAPP.ETMD_KPI02_TAB T
  LEFT JOIN IFSAPP.ETMD_KPI01_TAB T1
    ON T.KPI_ID = T1.KPI_ID
 WHERE T1.SITE = '{0}'
   AND T1.KPI_YEAR = {1}
   AND T.PERIOD_TYPE = 'M'
   AND T.PERIOD_VALUE = '{2}'
   AND (T.KPI_VALUEACT IS NULL OR T.STATUS_E = 'Release')";
                if (!string.IsNullOrEmpty(OracleOperation.GetSelectValue(string.Format(checkSql, site, year, month))))
                {
                    return "{\"flag\":false,\"msg\":\"[" + year + "-" + month + "]負責人未全部確認，無法鎖定！\"}";
                }
                string querySql = @"SELECT COUNT(*)
  FROM IFSAPP.ETMD_KPI02_TAB T
  LEFT JOIN IFSAPP.ETMD_KPI01_TAB T1
    ON T.KPI_ID = T1.KPI_ID
 WHERE T1.SITE = '{0}'
   AND T1.KPI_YEAR = {1}
   AND T.PERIOD_TYPE = 'M'
   AND T.PERIOD_VALUE = '{2}'
   AND T.IS_LOCK = 'Y'";
                string dataCount = OracleOperation.GetSelectValue(string.Format(querySql, site, year, month));
                if (dataCount == "0")
                {
                    return "{\"flag\":true,\"isLock\":false}";
                }
                else
                {
                    return "{\"flag\":true,\"isLock\":true}";
                }
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"操作失敗！\"}";
            }
        }
        /// <summary>
        /// （QA）鎖定/取消
        /// </summary>
        public string KpiLockOrCancelAct()
        {
            try
            {
                //string user = Session["Card_No"].ToString();
                var site = Request["site"];
                var year = Request["year"];
                var month = Request["month"];
                var flag = Request["flag"];
                string updateSql = @"UPDATE IFSAPP.ETMD_KPI02_TAB T
   SET T.IS_LOCK = '{4}'
 WHERE T.IS_LOCK = '{3}'
   AND T.PERIOD_TYPE = 'M'
   AND T.PERIOD_VALUE = '{2}'
   AND EXISTS (SELECT 1
          FROM IFSAPP.ETMD_KPI01_TAB T1
         WHERE T1.KPI_ID = T.KPI_ID
           AND T1.SITE = '{0}'
           AND T1.KPI_YEAR = {1})";
                OracleOperation.Exec(string.Format(updateSql, site, year, month, flag == "lock" ? "Y" : "N", flag == "lock" ? "N" : "Y"));
                return "{\"flag\":true,\"msg\":\"操作成功！\"}";
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"操作失敗！\"}";
            }
        }
        public string KpiMonthSel()
        {
            string str = "";
            for (int i = 1; i <= 12; i++)
            {
                if (DateTime.Now.Month - 1 == i || (DateTime.Now.Month == 1 && i == 12))
                {
                    str += "{ \"selected\": true, \"name\": \"" + i + "月\", \"id\": \"" + i + "\" },";
                }
                else
                {
                    str += "{ \"selected\": false, \"name\": \"" + i + "月\", \"id\": \"" + i + "\" },";
                }
            }
            return str.Remove(str.Length - 1);
        }
        #endregion

        #region 分子/分母來源
        /// <summary>
        /// 分子/分母來源View
        /// </summary>
        public ActionResult KpiActSource()
        {
            if (!Hpubilc.UserInvalid(Session["Card_No"]) || !Hpubilc.UserInvalid(Session["Dept"]) || !Hpubilc.UserInvalid(Session["PERMISSIONS"]))
            {
                return Content("<script>alert('登錄已過期，請重新登錄！');window.location.href='/Login';</script>");
            }
            ViewBag.PageDesc = "KPI";
            ViewBag.Page = Hpubilc.GetPageInfo(Session["Card_No"].ToString(), "Kpi/KpiMain").Tables[0].Rows;
            ViewBag.PageL2 = Hpubilc.GetPageInfoL2(Session["Card_No"].ToString(), "Kpi/KpiMain").Tables[0].Rows;
            ViewBag.Dept = GetDeptDt().Rows;
            ViewBag.Freq = GetFreq().Rows;
            ViewBag.Input = GetInput().Rows;
            ViewBag.KpiMonthSel = KpiMonthSel();
            DataSet dataSet = SearchDataAs("FP", true);
            ViewBag.KpiAsData = dataSet.Tables[1];
            ViewBag.KpiAsDataGroup = dataSet.Tables[0];
            ViewBag.Contract = Contract;
            return View();
        }
        /// <summary>
        /// KpiActSourceDataView
        /// </summary>
        public ActionResult BtnSearchClickAs()
        {
            if (!Hpubilc.UserInvalid(Session["Card_No"]) || !Hpubilc.UserInvalid(Session["Dept"]) || !Hpubilc.UserInvalid(Session["PERMISSIONS"]))
            {
                return Content("<script>alert('登錄已過期，請重新登錄！');window.location.href='/Login';</script>");
            }
            string flipPage = Request["flipPage"];
            ViewBag.Month = string.IsNullOrEmpty(Request["txtKpiMonth"]) ? "0" : Request["txtKpiMonth"];
            string vb_month = ViewBag.Month;
            if (vb_month == "0")
            {
                ViewBag.TdWidth = "160%";
                ViewBag.TfCs = "46";
            }
            else
            {
                switch (vb_month.Split(',').Count())
                {
                    case 1: ViewBag.TdWidth = "100%"; ViewBag.TfCs = "1"; break;
                    case 2: ViewBag.TdWidth = "100%"; ViewBag.TfCs = "1"; break;
                    case 3: ViewBag.TdWidth = "104.5%"; ViewBag.TfCs = "3"; break;
                    case 4: ViewBag.TdWidth = "110%"; ViewBag.TfCs = "3"; break;
                    case 5: ViewBag.TdWidth = "115.5%"; ViewBag.TfCs = "3"; break;
                    case 6: ViewBag.TdWidth = "121%"; ViewBag.TfCs = "10"; break;
                    case 7: ViewBag.TdWidth = "126.5%"; ViewBag.TfCs = "5"; break;
                    case 8: ViewBag.TdWidth = "132%"; ViewBag.TfCs = "5"; break;
                    case 9: ViewBag.TdWidth = "137.5%"; ViewBag.TfCs = "8"; break;
                    case 10: ViewBag.TdWidth = "143%"; ViewBag.TfCs = "8"; break;
                    case 11: ViewBag.TdWidth = "148.5%"; ViewBag.TfCs = "8"; break;
                    case 12: ViewBag.TdWidth = "154%"; ViewBag.TfCs = "8"; break;
                }
            }
            DataSet dataSet = SearchDataAs(flipPage);
            ViewBag.KpiAsData = dataSet.Tables[1];
            ViewBag.KpiAsDataGroup = dataSet.Tables[0];
            return PartialView("KpiActSourceData");
        }
        /// <summary>
        /// 查詢
        /// </summary>
        private DataSet SearchDataAs(string flipPage, bool firstQuery = false)
        {
            string querySql = @"SELECT T.KPI_ID,
       T1.SITE,
       T1.KPI_YEAR,
       T1.KPI_NO,
       T1.KPI_DESC,
       T1.KPI_TARGET,
       T1.KPI_FUNC,
       T1.INPUT_TYPE,
       T1.FREQ_DESC,
       T.PERIOD_TYPE,
       T.PERIOD_VALUE,
       T1.UOM_NM,
       T1.TREND_DESC,
       T.KPI_VALUE,
       T.KPI_VALUE_MD,
       T1.KPI_OWNER,
       T.KPI_VALUE_TYPE,
       T.DATA_SOURCE,
       T.DATA_SOURCE_DESC,
       T.CONTACT_PERSON,
       T.WHEN_TO_GET,
       T.WHEN_TO_GET_DESC,
       T.KPI_VALUE_DESC
  FROM IFSAPP.ETMD_KPI03_TAB T
  LEFT JOIN IFSAPP.ETMD_KPI01_TAB T1
    ON T.KPI_ID = T1.KPI_ID
  LEFT JOIN IFSAPP.ETMD_USER_TAB U
    ON T1.OWNER_ID = U.USER_ID
  LEFT JOIN IFSAPP.ETMD_DEPT_TAB D
    ON U.DEPT_ID = D.DEPT_ID
 WHERE 1 = 1";
            string querySqlGroup = @"SELECT T.KPI_ID,
       T1.SITE,
       T1.KPI_YEAR,
       T1.KPI_NO,
       T1.KPI_DESC,
       T1.KPI_TARGET,
       T1.KPI_FUNC,
       T1.INPUT_TYPE,
       T1.FREQ_DESC,
       T.PERIOD_TYPE,
       T1.KPI_OWNER,
       T.KPI_VALUE_TYPE,
       T.DATA_SOURCE,
       T.DATA_SOURCE_DESC,
       T.CONTACT_PERSON,
       T.WHEN_TO_GET,
       T.WHEN_TO_GET_DESC,
       T.KPI_VALUE_DESC,
       COUNT(*) TOTAL
  FROM IFSAPP.ETMD_KPI03_TAB T
  LEFT JOIN IFSAPP.ETMD_KPI01_TAB T1
    ON T.KPI_ID = T1.KPI_ID
  LEFT JOIN IFSAPP.ETMD_PERIOD_V PV
    ON T.PERIOD_TYPE = PV.CONF_NM
  LEFT JOIN IFSAPP.ETMD_USER_TAB U
    ON T1.OWNER_ID = U.USER_ID
  LEFT JOIN IFSAPP.ETMD_DEPT_TAB D
    ON U.DEPT_ID = D.DEPT_ID
 WHERE 1 = 1";
            string txtKpiSite = firstQuery ? Contract : Request["txtKpiSite"];
            string txtKpiYear = firstQuery ? DateTime.Now.Year.ToString() : Request["txtKpiYear"];
            string txtKpiNo = Request["txtKpiNo"];
            string txtKpiDesc = Request["txtKpiDesc"];
            string txtKpiTarget = Request["txtKpiTarget"];
            string txtKpiDept = Request["txtKpiDept"];
            string txtKpiPeriod = string.IsNullOrEmpty(Request["txtKpiPeriod"]) ? "M" : Request["txtKpiPeriod"];
            string txtKpiFreq = Request["txtKpiFreq"];
            string txtKpiInput = Request["txtKpiInput"];
            if (!string.IsNullOrEmpty(txtKpiSite))
            {
                querySql += " AND T1.SITE LIKE '%" + txtKpiSite + "%'";
                querySqlGroup += " AND T1.SITE LIKE '%" + txtKpiSite + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiYear))
            {
                querySql += " AND T1.KPI_YEAR LIKE '%" + txtKpiYear + "%'";
                querySqlGroup += " AND T1.KPI_YEAR LIKE '%" + txtKpiYear + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiNo))
            {
                querySql += " AND T1.KPI_NO LIKE '" + txtKpiNo + "'";
                querySqlGroup += " AND T1.KPI_NO LIKE '" + txtKpiNo + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiDesc))
            {
                querySql += " AND T1.KPI_DESC LIKE '%" + txtKpiDesc + "%'";
                querySqlGroup += " AND T1.KPI_DESC LIKE '%" + txtKpiDesc + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiTarget))
            {
                querySql += " AND T1.KPI_TARGET LIKE '%" + txtKpiTarget + "%'";
                querySqlGroup += " AND T1.KPI_TARGET LIKE '%" + txtKpiTarget + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiDept))
            {
                querySql += " AND D.DEPT_ID = '" + txtKpiDept + "'";
                querySqlGroup += " AND D.DEPT_ID = '" + txtKpiDept + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiPeriod))
            {
                querySql += " AND T.PERIOD_TYPE = '" + txtKpiPeriod + "'";
                querySqlGroup += " AND T.PERIOD_TYPE = '" + txtKpiPeriod + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiFreq))
            {
                querySql += " AND T1.FREQ_ID = '" + txtKpiFreq + "'";
                querySqlGroup += " AND T1.FREQ_ID = '" + txtKpiFreq + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiInput))
            {
                querySql += " AND T1.INPUT_ID = '" + txtKpiInput + "'";
                querySqlGroup += " AND T1.INPUT_ID = '" + txtKpiInput + "'";
            }
            //權限控制
            if (Session["PERMISSIONS"].ToString() != "1")
            {
                querySql += " AND D.DEPT_NO = '" + Session["Dept"].ToString() + "'";
                querySqlGroup += " AND D.DEPT_NO = '" + Session["Dept"].ToString() + "'";
            }
            querySql += " ORDER BY T1.KPI_NO, DECODE(T.PERIOD_TYPE, 'M', 1, 'Q', 2, 'H', 3, 'Y', 4), T.PERIOD_VALUE";
            querySqlGroup += @" GROUP BY T.KPI_ID,
          T1.SITE,
          T1.KPI_YEAR,
          T1.KPI_NO,
          T1.KPI_DESC,
          T1.KPI_TARGET,
          T1.KPI_FUNC,
          T1.INPUT_TYPE,
          T1.FREQ_DESC,
          T.PERIOD_TYPE,
          T1.KPI_OWNER,
          T.KPI_VALUE_TYPE,
          T.DATA_SOURCE,
          T.DATA_SOURCE_DESC,
          T.CONTACT_PERSON,
          T.WHEN_TO_GET,
          T.WHEN_TO_GET_DESC,
          T.KPI_VALUE_DESC
 ORDER BY T1.KPI_NO,
          DECODE(T.PERIOD_TYPE, 'M', 1, 'Q', 2, 'H', 3, 'Y', 4),
          DECODE(T.KPI_VALUE_TYPE, CHR(42176) || CHR(42092), 1, 2)";
            DataSet dataSet = OracleOperation.QueryData(querySqlGroup);

            //分頁
            string finSql = @"SELECT *
  FROM (SELECT ROWNUM RN, TD.*
          FROM ({0}) TD) T
 WHERE T.RN BETWEEN {1} AND {2}";
            int pagesIndex = Convert.ToInt32(string.IsNullOrEmpty(Request["pagesIndex"]) ? "1" : Request["pagesIndex"]);
            int pagesTo = Convert.ToInt32(string.IsNullOrEmpty(Request["pagesTo"]) ? pagesIndex.ToString() : Request["pagesTo"]);
            int pagesNum = Convert.ToInt32(string.IsNullOrEmpty(Request["pagesNum"]) ? "10" : Request["pagesNum"]);
            int indexF = 0;
            int indexT = 0;
            int totalNum = dataSet.Tables[0].Rows.Count;
            int maxPages = (int)Math.Ceiling((double)totalNum / (double)pagesNum);

            if (flipPage == "FP")
            {
                //首頁
                pagesIndex = 1;
                indexT = pagesNum;
                indexF = 1;
                finSql = string.Format(finSql, querySqlGroup, indexF, indexT);
            }
            else if (flipPage == "PP")
            {
                //上一頁
                pagesIndex = pagesIndex - 1 <= 0 ? 1 : pagesIndex - 1;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySqlGroup, indexF, indexT);
            }
            else if (flipPage == "NP")
            {
                //下一頁
                pagesIndex = pagesIndex + 1 > maxPages ? maxPages : pagesIndex + 1;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySqlGroup, indexF, indexT);
            }
            else if (flipPage == "LP")
            {
                //尾頁
                pagesIndex = maxPages;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySqlGroup, indexF, indexT);
            }
            else if (flipPage == "TP")
            {
                //轉頁
                pagesTo = pagesTo > maxPages ? maxPages : pagesTo;
                pagesTo = pagesTo <= 0 ? 1 : pagesTo;
                pagesIndex = pagesTo;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySqlGroup, indexF, indexT);
            }
            else if (flipPage == "CP")
            {
                //當前頁
                //pagesIndex = pagesIndex;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySqlGroup, indexF, indexT);
            }

            ViewBag.PagesIndex = pagesIndex;
            ViewBag.PagesCount = maxPages;
            ViewBag.DataNums = totalNum;
            ViewBag.pagesNum = pagesNum;
            ViewBag.IsLock = "";
            DataSet finDataSet = new DataSet();
            dataSet = OracleOperation.QueryData(finSql);
            DataTable dataTable = dataSet.Tables[0];
            dataTable.TableName = "AsGroup";
            finDataSet.Tables.Add(dataTable.Copy());
            dataSet = OracleOperation.QueryData(querySql);
            dataTable = dataSet.Tables[0];
            dataTable.TableName = "As";
            finDataSet.Tables.Add(dataTable.Copy());
            return finDataSet;
        }
        #endregion

        #region Flow簽核
        public string KpiFlowIni()
        {
            try
            {
                string contract = Request["site"];
                string year = Request["year"];
                string checkSql = @"select 1
  from IFSAPP.ETEF_FLOW_INFO_TAB
 where FLOW_APPROVE_STATE not in ('已抽單', '不同意')
   AND nvl(FLOW_THROW_CODE, 'N') = 'Y'
   and ref_key6 = '*'
   and ref_key5 = '*'
   and ref_key4 = '*'
   and ref_key3 = '*'
   and ref_key2 = '{1}'
   and ref_key1 = '{0}'
   and lu_name = 'KpiTarget'";
                string msg = OracleOperation.GetSelectValue(string.Format(checkSql, year, Contract));
                if (string.IsNullOrEmpty(msg))
                {
                    IDataParameter[] parmeterdata = new OracleParameter[2];
                    parmeterdata[0] = new OracleParameter("kpi_year_", OracleDbType.Varchar2, 80);
                    parmeterdata[0].Value = year;

                    parmeterdata[1] = new OracleParameter("contract_", OracleDbType.Varchar2, 80);
                    parmeterdata[1].Value = Contract;
                    OracleOperation.ExecProcedure("ifsapp.Etmd_Kpi01_api.Ini_Flow_Target", parmeterdata);
                    return "{\"flag\":true,\"msg\":\"操作成功！\"}";
                }
                else
                {
                    return "{\"flag\":false,\"msg\":\"簽核已啟動！\"}";
                }
            }
            catch (Exception e)
            {
                return "{\"flag\":false,\"msg\":\"操作失敗！（" + e.Message + "）\"}";
            }
        }
        public string KpiFlowIniAct()
        {
            try
            {
                bool othFlag = Convert.ToBoolean(Request["oth_flag"]);
                string contract = Request["site"];
                string month = Request["month"];
                string checkSql = @"select 1
  from IFSAPP.ETEF_FLOW_INFO_TAB
 where FLOW_APPROVE_STATE not in ('已抽單', '不同意')
   AND nvl(FLOW_THROW_CODE, 'N') = 'Y'
   and ref_key6 = '*'
   and ref_key5 = '*'
   and ref_key4 = '*'
   and ref_key3 = '*'
   and ref_key2 = '{1}'
   and ref_key1 = '{0}'
   and lu_name = 'KpiActual'";
                string msg = OracleOperation.GetSelectValue(string.Format(checkSql, month, Contract));
                if (string.IsNullOrEmpty(msg) || othFlag)
                {
                    IDataParameter[] parmeterdata = new OracleParameter[2];
                    parmeterdata[0] = new OracleParameter("kpi_month_", OracleDbType.Varchar2, 80);
                    parmeterdata[0].Value = month;

                    parmeterdata[1] = new OracleParameter("contract_", OracleDbType.Varchar2, 80);
                    parmeterdata[1].Value = Contract;
                    OracleOperation.ExecProcedure("ifsapp.Etmd_Kpi02_api.Ini_Flow_Actual", parmeterdata);
                    return "{\"flag\":true,\"msg\":\"操作成功！\"}";
                }
                else
                {
                    return "{\"flag\":false,\"msg\":\"簽核已啟動！\"}";
                }
            }
            catch (Exception e)
            {
                return "{\"flag\":false,\"msg\":\"操作失敗！（" + e.Message + "）\"}";
            }
        }
        #endregion

        #region 數據導出
        public string ExportTarget()
        {
            #region datatable
            string txtKpiSite = Request["txtKpiSite"];
            string txtKpiYear = Request["txtKpiYear"];
            string txtKpiNo = Request["txtKpiNo"];
            string txtKpiDesc = Request["txtKpiDesc"];
            string txtKpiTarget = Request["txtKpiTarget"];
            string txtKpiDept = Request["txtKpiDept"];
            string txtKpiVersion = Request["txtKpiVersion"];
            string txtKpiTrend = Request["txtKpiTrend"];
            string TxtKpiStatus = Request["TxtKpiStatus"];
            string querySql = @"SELECT T.VERSION_NO,
       T.SITE,
       T.KPI_YEAR,
       T.KPI_NO,
       T.KPI_DESC,
       T.KPI_TARGET,
       T1.KPI_VALUE PRE_KPI_VALUE,
       T.KPI_VALUE,
       T.UOM_NM,
       CASE
         WHEN T.TREND_ID = 58 AND T.KPI_VALUE = T1.KPI_VALUE THEN
          CHR(41304)
         WHEN T.TREND_ID = 58 AND T.KPI_VALUE > T1.KPI_VALUE THEN
          CHR(41460)
         WHEN T.TREND_ID = 58 AND T.KPI_VALUE < T1.KPI_VALUE THEN
          CHR(41461)
         WHEN T.TREND_ID = 57 AND T.KPI_VALUE = T1.KPI_VALUE THEN
          CHR(41304)
         WHEN T.TREND_ID = 57 AND T.KPI_VALUE > T1.KPI_VALUE THEN
          CHR(41461)
         WHEN T.TREND_ID = 57 AND T.KPI_VALUE < T1.KPI_VALUE THEN
          CHR(41460)
       END VALUE_TREND,
       T.TREND_DESC,
       T.KPI_FUNC,
       T.FREQ_DESC,
       T.STATUS_E,
       T.KPI_OWNER
  FROM IFSAPP.ETMD_KPI01_TAB T
  LEFT JOIN IFSAPP.ETMD_KPI01_LAST_V T1
    ON T.SITE = T1.SITE
   AND T.KPI_NO = T1.KPI_NO
   AND T.KPI_YEAR - 1 = T1.KPI_YEAR
  LEFT JOIN IFSAPP.ETMD_USER_TAB U
    ON T.OWNER_ID = U.USER_ID
  LEFT JOIN IFSAPP.ETMD_DEPT_TAB D
    ON U.DEPT_ID = D.DEPT_ID
 WHERE 1 = 1";
            if (!string.IsNullOrEmpty(txtKpiSite))
            {
                querySql += " AND UPPER(T.SITE) LIKE '%" + txtKpiSite.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiYear))
            {
                querySql += " AND T.KPI_YEAR LIKE '%" + txtKpiYear + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiNo))
            {
                querySql += " AND T.KPI_NO LIKE '" + txtKpiNo + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiDesc))
            {
                querySql += " AND UPPER(T.KPI_DESC) LIKE '%" + txtKpiDesc.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiTarget))
            {
                querySql += " AND UPPER(T.KPI_TARGET) LIKE '%" + txtKpiTarget.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiDept))
            {
                querySql += " AND D.DEPT_ID = '" + txtKpiDept + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiVersion))
            {
                querySql += " AND T.VERSION_NO = '" + txtKpiVersion + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiTrend))
            {
                querySql += @" AND (CASE
         WHEN T.TREND_ID = 58 AND T.KPI_VALUE = T1.KPI_VALUE THEN
          CHR(41304)
         WHEN T.TREND_ID = 58 AND T.KPI_VALUE > T1.KPI_VALUE THEN
          CHR(41460)
         WHEN T.TREND_ID = 58 AND T.KPI_VALUE < T1.KPI_VALUE THEN
          CHR(41461)
         WHEN T.TREND_ID = 57 AND T.KPI_VALUE = T1.KPI_VALUE THEN
          CHR(41304)
         WHEN T.TREND_ID = 57 AND T.KPI_VALUE > T1.KPI_VALUE THEN
          CHR(41461)
         WHEN T.TREND_ID = 57 AND T.KPI_VALUE < T1.KPI_VALUE THEN
          CHR(41460)
       END) = '" + txtKpiTrend + "'";
            }
            if (!string.IsNullOrEmpty(TxtKpiStatus))
            {
                querySql += " AND T.STATUS_E = '" + TxtKpiStatus + "'";
            }
            //權限控制
            if (Session["PERMISSIONS"].ToString() != "1")
            {
                querySql += " AND D.DEPT_NO = '" + Session["Dept"].ToString() + "'";
            }
            querySql += " ORDER BY T.SITE, T.KPI_YEAR, T.KPI_NO, T.VERSION_NO";
            DataTable dataTable = OracleOperation.QueryData(querySql).Tables[0];
            #endregion

            string url = HostingEnvironment.MapPath("~/") + "Template/";
            //导出文件路径
            long timestamp = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
            if (!Directory.Exists(url + timestamp.ToString()))
            {
                Directory.CreateDirectory(url + timestamp.ToString());
            }
            string outputPath = url + timestamp.ToString() + "/Kpi目標匯總表.xlsx";
            //Excel處理
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Kpi目標匯總表");
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderTop = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.Alignment = HorizontalAlignment.Center;
            style.FillForegroundColor = 0;
            style.FillPattern = FillPattern.SolidForeground;
            ((XSSFColor)style.FillForegroundColorColor).SetRgb(new byte[] { 217, 217, 217 });
            IFont font = workbook.CreateFont();
            font.IsBold = true;
            style.SetFont(font);
            sheet.CreateRow(0).CreateCell(0).SetCellValue("版本號");
            sheet.GetRow(0).CreateCell(1).SetCellValue("域");
            sheet.GetRow(0).CreateCell(2).SetCellValue("年度");
            sheet.GetRow(0).CreateCell(3).SetCellValue("NO");
            sheet.GetRow(0).CreateCell(4).SetCellValue("流程");
            sheet.GetRow(0).CreateCell(5).SetCellValue("績效指標");
            sheet.GetRow(0).CreateCell(6).SetCellValue("上年度KPI指標");
            sheet.GetRow(0).CreateCell(7).SetCellValue("本年度KPI指標");
            sheet.GetRow(0).CreateCell(8).SetCellValue("單位");
            sheet.GetRow(0).CreateCell(9).SetCellValue("趨勢");
            sheet.GetRow(0).CreateCell(10).SetCellValue("數值趨勢");
            sheet.GetRow(0).CreateCell(11).SetCellValue("公式");
            sheet.GetRow(0).CreateCell(12).SetCellValue("衡量頻率");
            sheet.GetRow(0).CreateCell(13).SetCellValue("狀態");
            sheet.GetRow(0).CreateCell(14).SetCellValue("流程負責人");
            for (int i = 0; i < 15; i++)
            {
                sheet.GetRow(0).GetCell(i).CellStyle = style;
            }
            if (dataTable.Rows.Count > 0)
            {
                int index = 1;
                ICellStyle style2 = workbook.CreateCellStyle();
                style2.BorderTop = BorderStyle.Thin;
                style2.BorderRight = BorderStyle.Thin;
                style2.BorderBottom = BorderStyle.Thin;
                style2.BorderLeft = BorderStyle.Thin;
                foreach (DataRow item in dataTable.Rows)
                {
                    sheet.CreateRow(index).CreateCell(0).SetCellValue(item["VERSION_NO"].ToString());
                    sheet.GetRow(index).CreateCell(1).SetCellValue(item["SITE"].ToString());
                    sheet.GetRow(index).CreateCell(2).SetCellValue(item["KPI_YEAR"].ToString());
                    sheet.GetRow(index).CreateCell(3).SetCellValue(item["KPI_NO"].ToString());
                    sheet.GetRow(index).CreateCell(4).SetCellValue(item["KPI_DESC"].ToString());
                    sheet.GetRow(index).CreateCell(5).SetCellValue(item["KPI_TARGET"].ToString());
                    sheet.GetRow(index).CreateCell(6).SetCellValue(item["PRE_KPI_VALUE"].ToString());
                    sheet.GetRow(index).CreateCell(7).SetCellValue(item["KPI_VALUE"].ToString());
                    sheet.GetRow(index).CreateCell(8).SetCellValue(item["UOM_NM"].ToString());
                    sheet.GetRow(index).CreateCell(9).SetCellValue(item["VALUE_TREND"].ToString());
                    sheet.GetRow(index).CreateCell(10).SetCellValue(item["TREND_DESC"].ToString());
                    sheet.GetRow(index).CreateCell(11).SetCellValue(item["KPI_FUNC"].ToString());
                    sheet.GetRow(index).CreateCell(12).SetCellValue(item["FREQ_DESC"].ToString());
                    sheet.GetRow(index).CreateCell(13).SetCellValue(item["STATUS_E"].ToString());
                    sheet.GetRow(index).CreateCell(14).SetCellValue(item["KPI_OWNER"].ToString());
                    for (int i = 0; i < 15; i++)
                    {
                        sheet.GetRow(index).GetCell(i).CellStyle = style2;
                    }
                    index++;
                }
            }
            sheet.SetColumnWidth(4, 30 * 256);
            sheet.SetColumnWidth(5, 30 * 256);
            sheet.SetColumnWidth(6, 15 * 256);
            sheet.SetColumnWidth(7, 15 * 256);
            sheet.SetColumnWidth(10, 10 * 256);
            sheet.SetColumnWidth(11, 30 * 256);
            sheet.SetColumnWidth(12, 10 * 256);
            sheet.SetColumnWidth(14, 12 * 256);

            string colIndex = Request["index"];
            for (int i = 0; i < 15; i++)
            {
                if (!colIndex.Split(',').Contains(i.ToString()))
                {
                    sheet.SetColumnHidden(i, true);
                }
            }

            FileStream file = new FileStream(outputPath, FileMode.Create);
            workbook.Write(file);
            file.Close();
            return "{\"flag\":true,\"msg\":\"" + timestamp.ToString() + "\"}";
        }
        public string ExportActual()
        {
            #region datatable
            string querySql = @"select t.KPI_ID,
       t.SITE,
       t.KPI_YEAR,
       t.KPI_NO,
       t.KPI_DESC,
       t.KPI_TARGET,
       t.KPI_FUNC,
       t.INPUT_TYPE,
       t.FREQ_DESC,
       t.V_FORM_DESC,
       t.TREND_DESC,
       t.CONF_V2,
       t.VALUE_TYPE,
       t.YEARLY_TYPE,
       t.KPI_OWNER,
       t.MONTH_1,
       t.MONTH_2,
       t.MONTH_3,
       t.MONTH_4,
       t.MONTH_5,
       t.MONTH_6,
       t.MONTH_7,
       t.MONTH_8,
       t.MONTH_9,
       t.MONTH_10,
       t.MONTH_11,
       t.MONTH_12,
       t.MONTH_1_DIFF,
       t.MONTH_2_DIFF,
       t.MONTH_3_DIFF,
       t.MONTH_4_DIFF,
       t.MONTH_5_DIFF,
       t.MONTH_6_DIFF,
       t.MONTH_7_DIFF,
       t.MONTH_8_DIFF,
       t.MONTH_9_DIFF,
       t.MONTH_10_DIFF,
       t.MONTH_11_DIFF,
       t.MONTH_12_DIFF,
       t.MONTH_1_IMP,
       t.MONTH_2_IMP,
       t.MONTH_3_IMP,
       t.MONTH_4_IMP,
       t.MONTH_5_IMP,
       t.MONTH_6_IMP,
       t.MONTH_7_IMP,
       t.MONTH_8_IMP,
       t.MONTH_9_IMP,
       t.MONTH_10_IMP,
       t.MONTH_11_IMP,
       t.MONTH_12_IMP,
       t.MONTH_1_ECD,
       t.MONTH_2_ECD,
       t.MONTH_3_ECD,
       t.MONTH_4_ECD,
       t.MONTH_5_ECD,
       t.MONTH_6_ECD,
       t.MONTH_7_ECD,
       t.MONTH_8_ECD,
       t.MONTH_9_ECD,
       t.MONTH_10_ECD,
       t.MONTH_11_ECD,
       t.MONTH_12_ECD,
       t.MONTH_1_REMARK,
       t.MONTH_2_REMARK,
       t.MONTH_3_REMARK,
       t.MONTH_4_REMARK,
       t.MONTH_5_REMARK,
       t.MONTH_6_REMARK,
       t.MONTH_7_REMARK,
       t.MONTH_8_REMARK,
       t.MONTH_9_REMARK,
       t.MONTH_10_REMARK,
       t.MONTH_11_REMARK,
       t.MONTH_12_REMARK,
       t.yearly_result,
       t.unqty
  from IFSAPP.ETMD_KPI_MONTH_SUM_V t
 where 1 = 1";
            string txtKpiSite = string.IsNullOrEmpty(Request["txtKpiSite"]) ? Contract : Request["txtKpiSite"];
            string txtKpiYear = string.IsNullOrEmpty(Request["txtKpiYear"]) ? (DateTime.Now.Month == 1 ? (DateTime.Now.Year - 1).ToString() : DateTime.Now.Year.ToString()) : Request["txtKpiYear"];
            string txtKpiNo = Request["txtKpiNo"];
            string txtKpiDesc = Request["txtKpiDesc"];
            string txtKpiTarget = Request["txtKpiTarget"];
            string txtKpiDept = Request["txtKpiDept"];
            string txtKpiPeriod = string.IsNullOrEmpty(Request["txtKpiPeriod"]) ? "M" : Request["txtKpiPeriod"];
            string txtKpiFreq = Request["txtKpiFreq"];
            string txtKpiInput = Request["txtKpiInput"];
            string txtKpiResult = Request["txtKpiResult"];
            if (!string.IsNullOrEmpty(txtKpiSite))
            {
                querySql += " AND UPPER(T.SITE) LIKE '%" + txtKpiSite.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiYear))
            {
                querySql += " AND T.KPI_YEAR LIKE '%" + txtKpiYear + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiNo))
            {
                querySql += " AND T.KPI_NO LIKE '" + txtKpiNo + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiDesc))
            {
                querySql += " AND UPPER(T.KPI_DESC) LIKE '%" + txtKpiDesc.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiTarget))
            {
                querySql += " AND UPPER(T.KPI_TARGET) LIKE '%" + txtKpiTarget.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiDept))
            {
                querySql += " AND t.DEPT_ID = '" + txtKpiDept + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiPeriod))
            {
                querySql += " AND T.PERIOD_TYPE = '" + txtKpiPeriod + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiFreq))
            {
                querySql += " AND T.FREQ_ID = '" + txtKpiFreq + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiInput))
            {
                querySql += " AND T.INPUT_ID = '" + txtKpiInput + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiResult))
            {
                querySql += " AND Get_CurrMonth_Result(T.KPI_ID) = '" + txtKpiResult + "'";
            }
            //權限控制
            if (Session["PERMISSIONS"].ToString() != "1")
            {
                querySql += " AND t.DEPT_NO = '" + Session["Dept"].ToString() + "'";
            }
            DataTable dataTable = OracleOperation.QueryData(querySql).Tables[0];
            #endregion

            string url = HostingEnvironment.MapPath("~/") + "Template/";
            //导出文件路径
            long timestamp = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
            if (!Directory.Exists(url + timestamp.ToString()))
            {
                Directory.CreateDirectory(url + timestamp.ToString());
            }
            string outputPath = url + timestamp.ToString() + "/Kpi實際匯總表.xlsx";
            //Excel處理
            //新建Excel
            XSSFWorkbook workbook = new XSSFWorkbook();
            //新建sheet
            ISheet sheet = workbook.CreateSheet("Kpi實際匯總表");
            //標題樣式
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderTop = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.Alignment = HorizontalAlignment.Center;
            style.FillForegroundColor = 0;
            style.FillPattern = FillPattern.SolidForeground;
            ((XSSFColor)style.FillForegroundColorColor).SetRgb(new byte[] { 217, 217, 217 });
            IFont font = workbook.CreateFont();
            font.IsBold = true;
            style.SetFont(font);
            //標題內容
            sheet.CreateRow(0).CreateCell(0).SetCellValue("域");
            sheet.GetRow(0).CreateCell(1).SetCellValue("年度");
            sheet.GetRow(0).CreateCell(2).SetCellValue("NO");
            sheet.GetRow(0).CreateCell(3).SetCellValue("流程");
            sheet.GetRow(0).CreateCell(4).SetCellValue("績效指標");
            sheet.GetRow(0).CreateCell(5).SetCellValue("公式");
            sheet.GetRow(0).CreateCell(6).SetCellValue("輸入方式");
            sheet.GetRow(0).CreateCell(7).SetCellValue("衡量頻率");
            sheet.GetRow(0).CreateCell(8).SetCellValue("數值形式");
            sheet.GetRow(0).CreateCell(9).SetCellValue("數值趨勢");
            sheet.GetRow(0).CreateCell(10).SetCellValue("週期類別");
            sheet.GetRow(0).CreateCell(11).SetCellValue("流程負責人");
            sheet.GetRow(0).CreateCell(12).SetCellValue("年度");
            sheet.GetRow(0).CreateCell(13).SetCellValue("年度結果");
            sheet.GetRow(0).CreateCell(14).SetCellValue("未達標次數");
            sheet.GetRow(0).CreateCell(15).SetCellValue("Target/Actual");
            int index_oth = 16;
            for (int i = 16; i < 28; i++)
            {
                sheet.GetRow(0).CreateCell(index_oth).SetCellValue((i - 15).ToString() + "月");
                sheet.GetRow(0).CreateCell(index_oth + 1).SetCellValue("備註");
                index_oth += 2;
            }
            //填充標題樣式
            for (int i = 0; i < 40; i++)
            {
                sheet.GetRow(0).GetCell(i).CellStyle = style;
            }
            //數據內容填充
            int index = 1;
            string percentStr = "%";
            if (dataTable.Rows.Count > 0)
            {
                index = 1;
                //各種判定結果樣式
                ICellStyle style2 = workbook.CreateCellStyle();
                style2.BorderTop = BorderStyle.Thin;
                style2.BorderRight = BorderStyle.Thin;
                style2.BorderBottom = BorderStyle.Thin;
                style2.BorderLeft = BorderStyle.Thin;
                style2.VerticalAlignment = VerticalAlignment.Center;
                style2.WrapText = true;

                string color;
                ICellStyle style3 = workbook.CreateCellStyle();
                style3.BorderTop = BorderStyle.Thin;
                style3.BorderRight = BorderStyle.Thin;
                style3.BorderBottom = BorderStyle.Thin;
                style3.BorderLeft = BorderStyle.Thin;
                style3.VerticalAlignment = VerticalAlignment.Center;
                style3.FillForegroundColor = 0;
                style3.FillPattern = FillPattern.SolidForeground;
                ((XSSFColor)style3.FillForegroundColorColor).SetRgb(new byte[] { 198, 239, 206 });

                ICellStyle style4 = workbook.CreateCellStyle();
                style4.BorderTop = BorderStyle.Thin;
                style4.BorderRight = BorderStyle.Thin;
                style4.BorderBottom = BorderStyle.Thin;
                style4.BorderLeft = BorderStyle.Thin;
                style4.VerticalAlignment = VerticalAlignment.Center;
                style4.FillForegroundColor = 0;
                style4.FillPattern = FillPattern.SolidForeground;
                ((XSSFColor)style4.FillForegroundColorColor).SetRgb(new byte[] { 255, 199, 206 });

                ICellStyle style5 = workbook.CreateCellStyle();
                style5.BorderTop = BorderStyle.Thin;
                style5.BorderRight = BorderStyle.Thin;
                style5.BorderBottom = BorderStyle.Thin;
                style5.BorderLeft = BorderStyle.Thin;
                style5.VerticalAlignment = VerticalAlignment.Center;
                style5.FillForegroundColor = 0;
                style5.FillPattern = FillPattern.SolidForeground;
                ((XSSFColor)style5.FillForegroundColorColor).SetRgb(new byte[] { 255, 235, 156 });
                foreach (DataRow item in dataTable.Rows)
                {
                    //填充具體數據
                    sheet.CreateRow(index).CreateCell(0).SetCellValue(item["SITE"].ToString());
                    sheet.GetRow(index).CreateCell(1).SetCellValue(item["KPI_YEAR"].ToString());
                    sheet.GetRow(index).CreateCell(2).SetCellValue(item["KPI_NO"].ToString());
                    sheet.GetRow(index).CreateCell(3).SetCellValue(item["KPI_DESC"].ToString());
                    sheet.GetRow(index).CreateCell(4).SetCellValue(item["KPI_TARGET"].ToString());
                    sheet.GetRow(index).CreateCell(5).SetCellValue(item["KPI_FUNC"].ToString());
                    sheet.GetRow(index).CreateCell(6).SetCellValue(item["INPUT_TYPE"].ToString());
                    sheet.GetRow(index).CreateCell(7).SetCellValue(item["FREQ_DESC"].ToString());
                    sheet.GetRow(index).CreateCell(8).SetCellValue(item["V_FORM_DESC"].ToString());
                    sheet.GetRow(index).CreateCell(9).SetCellValue(item["TREND_DESC"].ToString());
                    sheet.GetRow(index).CreateCell(10).SetCellValue(item["CONF_V2"].ToString());
                    sheet.GetRow(index).CreateCell(11).SetCellValue(item["KPI_OWNER"].ToString());
                    sheet.GetRow(index).CreateCell(12).SetCellValue(item["YEARLY_TYPE"].ToString());
                    sheet.GetRow(index).CreateCell(13).SetCellValue(item["YEARLY_RESULT"].ToString());
                    sheet.GetRow(index).CreateCell(14).SetCellValue(item["UNQTY"].ToString());
                    sheet.GetRow(index).CreateCell(15).SetCellValue(item["VALUE_TYPE"].ToString());

                    index_oth = 16;
                    for (int i = 16; i < 28; i++)
                    {
                        if (item["V_FORM_DESC"].ToString() == "百分比" && !string.IsNullOrEmpty(item["MONTH_" + (i - 15).ToString()].ToString()) && item["MONTH_" + (i - 15).ToString()].ToString() != "NA")
                        {
                            sheet.GetRow(index).CreateCell(index_oth).SetCellValue(item["MONTH_" + (i - 15).ToString()].ToString() + percentStr);
                        }
                        else
                        {
                            sheet.GetRow(index).CreateCell(index_oth).SetCellValue(item["MONTH_" + (i - 15).ToString()].ToString());
                        }
                        sheet.GetRow(index).CreateCell(index_oth + 1).SetCellValue(item["MONTH_" + (i - 15).ToString() + "_REMARK"].ToString());
                        index_oth += 2;
                    }
                    //不同結果樣式填充
                    index_oth = 16;
                    for (int i = 0; i < 40; i++)
                    {
                        if (index % 2 == 0 && i >= 16 && i < 28)
                        {
                            color = OracleOperation.GetSelectValue("select ifsapp.Etmd_Kpi02_api.Get_Result_Color(" + item["KPI_ID"].ToString() + ",'" + (i - 15).ToString().PadLeft(2, '0') + "') from dual");
                            if (color == "GREEN")
                            {
                                sheet.GetRow(index).GetCell(index_oth).CellStyle = style3;
                            }
                            else if (color == "RED")
                            {
                                sheet.GetRow(index).GetCell(index_oth).CellStyle = style4;
                            }
                            else if (color == "YELLOW")
                            {
                                sheet.GetRow(index).GetCell(index_oth).CellStyle = style5;
                            }
                            else
                            {
                                sheet.GetRow(index).GetCell(index_oth).CellStyle = style2;
                            }
                            sheet.GetRow(index).GetCell(index_oth + 1).CellStyle = style2;
                            index_oth += 2;
                        }
                        else
                        {
                            sheet.GetRow(index).GetCell(i).CellStyle = style2;
                        }
                    }
                    index++;
                }
            }
            //設置寬度
            sheet.SetColumnWidth(3, 30 * 256);
            sheet.SetColumnWidth(4, 30 * 256);
            sheet.SetColumnWidth(5, 30 * 256);
            sheet.SetColumnWidth(6, 10 * 256);
            sheet.SetColumnWidth(7, 10 * 256);
            sheet.SetColumnWidth(8, 10 * 256);
            sheet.SetColumnWidth(9, 10 * 256);
            sheet.SetColumnWidth(10, 10 * 256);
            sheet.SetColumnWidth(11, 12 * 256);
            sheet.SetColumnWidth(13, 10 * 256);
            sheet.SetColumnWidth(14, 12 * 256);
            sheet.SetColumnWidth(15, 15 * 256);
            for (int i = 17; i < 40; i += 2)
            {
                sheet.SetColumnWidth(i, 20 * 256);
            }
            //設置隱藏列以及單元格合併
            string colIndex = Request["index"];
            for (int i = 0; i < 15; i++)
            {
                if (!colIndex.Split(',').Contains(i.ToString()))
                {
                    sheet.SetColumnHidden(i, true);
                }
                if (i == 3)
                {
                    continue;
                }
                for (int j = 1; j <= dataTable.Rows.Count / 2; j++)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(j * 2 - 1, j * 2, i, i));
                }
            }
            bool hideFlag;
            for (int i = 17; i < 40; i += 2)
            {
                hideFlag = true;
                for (int j = 1; j <= dataTable.Rows.Count / 2; j++)
                {
                    if (!string.IsNullOrEmpty(sheet.GetRow(j * 2).GetCell(i).StringCellValue) && hideFlag)
                    {
                        hideFlag = false;
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(j * 2 - 1, j * 2, i, i));
                }
                sheet.SetColumnHidden(i, hideFlag);
            }

            if (index > 1)
            {
                string tmpStr = sheet.GetRow(1).GetCell(3).StringCellValue;
                int fr = 1;
                for (int i = 1; i < index; i++)
                {
                    if (tmpStr != sheet.GetRow(i).GetCell(3).StringCellValue)
                    {
                        tmpStr = sheet.GetRow(i).GetCell(3).StringCellValue;
                        sheet.AddMergedRegion(new CellRangeAddress(fr, i - 1, 3, 3));
                        fr = i;
                    }
                }
                sheet.AddMergedRegion(new CellRangeAddress(fr, index - 1, 3, 3));
            }

            FileStream file = new FileStream(outputPath, FileMode.Create);
            workbook.Write(file);
            file.Close();
            return "{\"flag\":true,\"msg\":\"" + timestamp.ToString() + "\"}";
        }
        public string ExportActualFailed()
        {
            #region datatable
            string querySql = @"select t.KPI_ID,
       t.SITE,
       t.KPI_YEAR,
       t.KPI_NO,
       t.KPI_DESC,
       t.KPI_TARGET,
       t.KPI_FUNC,
       t.INPUT_TYPE,
       t.FREQ_DESC,
       t.V_FORM_DESC,
       t.TREND_DESC,
       t.CONF_V2,
       t.VALUE_TYPE,
       t.YEARLY_TYPE,
       t.KPI_OWNER,
       t.MONTH_1,
       t.MONTH_2,
       t.MONTH_3,
       t.MONTH_4,
       t.MONTH_5,
       t.MONTH_6,
       t.MONTH_7,
       t.MONTH_8,
       t.MONTH_9,
       t.MONTH_10,
       t.MONTH_11,
       t.MONTH_12,
       t.MONTH_1_DIFF,
       t.MONTH_2_DIFF,
       t.MONTH_3_DIFF,
       t.MONTH_4_DIFF,
       t.MONTH_5_DIFF,
       t.MONTH_6_DIFF,
       t.MONTH_7_DIFF,
       t.MONTH_8_DIFF,
       t.MONTH_9_DIFF,
       t.MONTH_10_DIFF,
       t.MONTH_11_DIFF,
       t.MONTH_12_DIFF,
       t.MONTH_1_IMP,
       t.MONTH_2_IMP,
       t.MONTH_3_IMP,
       t.MONTH_4_IMP,
       t.MONTH_5_IMP,
       t.MONTH_6_IMP,
       t.MONTH_7_IMP,
       t.MONTH_8_IMP,
       t.MONTH_9_IMP,
       t.MONTH_10_IMP,
       t.MONTH_11_IMP,
       t.MONTH_12_IMP,
       t.MONTH_1_ECD,
       t.MONTH_2_ECD,
       t.MONTH_3_ECD,
       t.MONTH_4_ECD,
       t.MONTH_5_ECD,
       t.MONTH_6_ECD,
       t.MONTH_7_ECD,
       t.MONTH_8_ECD,
       t.MONTH_9_ECD,
       t.MONTH_10_ECD,
       t.MONTH_11_ECD,
       t.MONTH_12_ECD,
       t.MONTH_1_REMARK,
       t.MONTH_2_REMARK,
       t.MONTH_3_REMARK,
       t.MONTH_4_REMARK,
       t.MONTH_5_REMARK,
       t.MONTH_6_REMARK,
       t.MONTH_7_REMARK,
       t.MONTH_8_REMARK,
       t.MONTH_9_REMARK,
       t.MONTH_10_REMARK,
       t.MONTH_11_REMARK,
       t.MONTH_12_REMARK,
       t.yearly_result,
       t.unqty
  from ifsapp.ETMD_KPI_MONTH_SUM_V t
 where 1 = 1";
            string txtKpiSite = string.IsNullOrEmpty(Request["txtKpiSite"]) ? Contract : Request["txtKpiSite"];
            string txtKpiYear = string.IsNullOrEmpty(Request["txtKpiYear"]) ? (DateTime.Now.Month == 1 ? (DateTime.Now.Year - 1).ToString() : DateTime.Now.Year.ToString()) : Request["txtKpiYear"];
            string txtKpiNo = Request["txtKpiNo"];
            string txtKpiDesc = Request["txtKpiDesc"];
            string txtKpiTarget = Request["txtKpiTarget"];
            string txtKpiDept = Request["txtKpiDept"];
            string txtKpiPeriod = string.IsNullOrEmpty(Request["txtKpiPeriod"]) ? "M" : Request["txtKpiPeriod"];
            string txtKpiFreq = Request["txtKpiFreq"];
            string txtKpiInput = Request["txtKpiInput"];
            string txtKpiResult = Request["txtKpiResult"];
            if (!string.IsNullOrEmpty(txtKpiSite))
            {
                querySql += " AND UPPER(T.SITE) LIKE '%" + txtKpiSite.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiYear))
            {
                querySql += " AND T.KPI_YEAR LIKE '%" + txtKpiYear + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiNo))
            {
                querySql += " AND T.KPI_NO LIKE '" + txtKpiNo + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiDesc))
            {
                querySql += " AND UPPER(T.KPI_DESC) LIKE '%" + txtKpiDesc.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiTarget))
            {
                querySql += " AND UPPER(T.KPI_TARGET) LIKE '%" + txtKpiTarget.ToUpper() + "%'";
            }
            if (!string.IsNullOrEmpty(txtKpiDept))
            {
                querySql += " AND t.DEPT_ID = '" + txtKpiDept + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiPeriod))
            {
                querySql += " AND T.PERIOD_TYPE = '" + txtKpiPeriod + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiFreq))
            {
                querySql += " AND T.FREQ_ID = '" + txtKpiFreq + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiInput))
            {
                querySql += " AND T.INPUT_ID = '" + txtKpiInput + "'";
            }
            if (!string.IsNullOrEmpty(txtKpiResult))
            {
                querySql += " AND Get_CurrMonth_Result(T.KPI_ID) = '" + txtKpiResult + "'";
            }
            //權限控制
            if (Session["PERMISSIONS"].ToString() != "1")
            {
                querySql += " AND t.DEPT_NO = '" + Session["Dept"].ToString() + "'";
            }
            //未達成
            querySql += " AND t.unqty > 0";
            DataTable dataTable = OracleOperation.QueryData(querySql).Tables[0];
            #endregion

            string url = HostingEnvironment.MapPath("~/") + "Template/";
            //导出文件路径
            long timestamp = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
            if (!Directory.Exists(url + timestamp.ToString()))
            {
                Directory.CreateDirectory(url + timestamp.ToString());
            }
            string outputPath = url + timestamp.ToString() + "/Kpi差異分析表.xlsx";
            //Excel處理
            //新建Excel
            XSSFWorkbook workbook = new XSSFWorkbook();
            //新建sheet
            ISheet sheet = workbook.CreateSheet("Kpi差異分析表");
            //標題樣式
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderTop = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.Alignment = HorizontalAlignment.Center;
            style.FillForegroundColor = 0;
            style.FillPattern = FillPattern.SolidForeground;
            ((XSSFColor)style.FillForegroundColorColor).SetRgb(new byte[] { 217, 217, 217 });
            IFont font = workbook.CreateFont();
            font.IsBold = true;
            style.SetFont(font);
            //標題內容
            sheet.CreateRow(0).CreateCell(0).SetCellValue("域");
            sheet.GetRow(0).CreateCell(1).SetCellValue("年度");
            sheet.GetRow(0).CreateCell(2).SetCellValue("NO");
            sheet.GetRow(0).CreateCell(3).SetCellValue("流程");
            sheet.GetRow(0).CreateCell(4).SetCellValue("績效指標");
            sheet.GetRow(0).CreateCell(5).SetCellValue("公式");
            sheet.GetRow(0).CreateCell(6).SetCellValue("輸入方式");
            sheet.GetRow(0).CreateCell(7).SetCellValue("衡量頻率");
            sheet.GetRow(0).CreateCell(8).SetCellValue("數值形式");
            sheet.GetRow(0).CreateCell(9).SetCellValue("數值趨勢");
            sheet.GetRow(0).CreateCell(10).SetCellValue("週期類別");
            sheet.GetRow(0).CreateCell(11).SetCellValue("流程負責人");
            sheet.GetRow(0).CreateCell(12).SetCellValue("年度");
            sheet.GetRow(0).CreateCell(13).SetCellValue("年度結果");
            sheet.GetRow(0).CreateCell(14).SetCellValue("未達標數量");
            sheet.GetRow(0).CreateCell(15).SetCellValue("Target/Actual");
            int index_oth = 16;
            for (int i = 16; i < 28; i++)
            {
                sheet.GetRow(0).CreateCell(index_oth).SetCellValue((i - 15).ToString() + "月");
                sheet.GetRow(0).CreateCell(index_oth + 1).SetCellValue("差異分析");
                sheet.GetRow(0).CreateCell(index_oth + 2).SetCellValue("改善措施");
                sheet.GetRow(0).CreateCell(index_oth + 3).SetCellValue("ECD");
                sheet.GetRow(0).CreateCell(index_oth + 4).SetCellValue("備註");
                index_oth += 5;
            }
            //填充標題樣式
            for (int i = 0; i < 76; i++)
            {
                sheet.GetRow(0).GetCell(i).CellStyle = style;
            }
            //數據內容填充
            int index = 1;
            string percentStr = "%";
            if (dataTable.Rows.Count > 0)
            {
                index = 1;
                //各種判定結果樣式
                ICellStyle style2 = workbook.CreateCellStyle();
                style2.BorderTop = BorderStyle.Thin;
                style2.BorderRight = BorderStyle.Thin;
                style2.BorderBottom = BorderStyle.Thin;
                style2.BorderLeft = BorderStyle.Thin;
                style2.VerticalAlignment = VerticalAlignment.Center;
                style2.WrapText = true;

                string color;
                ICellStyle style3 = workbook.CreateCellStyle();
                style3.BorderTop = BorderStyle.Thin;
                style3.BorderRight = BorderStyle.Thin;
                style3.BorderBottom = BorderStyle.Thin;
                style3.BorderLeft = BorderStyle.Thin;
                style3.VerticalAlignment = VerticalAlignment.Center;
                style3.FillForegroundColor = 0;
                style3.FillPattern = FillPattern.SolidForeground;
                ((XSSFColor)style3.FillForegroundColorColor).SetRgb(new byte[] { 198, 239, 206 });

                ICellStyle style4 = workbook.CreateCellStyle();
                style4.BorderTop = BorderStyle.Thin;
                style4.BorderRight = BorderStyle.Thin;
                style4.BorderBottom = BorderStyle.Thin;
                style4.BorderLeft = BorderStyle.Thin;
                style4.VerticalAlignment = VerticalAlignment.Center;
                style4.FillForegroundColor = 0;
                style4.FillPattern = FillPattern.SolidForeground;
                ((XSSFColor)style4.FillForegroundColorColor).SetRgb(new byte[] { 255, 199, 206 });

                ICellStyle style5 = workbook.CreateCellStyle();
                style5.BorderTop = BorderStyle.Thin;
                style5.BorderRight = BorderStyle.Thin;
                style5.BorderBottom = BorderStyle.Thin;
                style5.BorderLeft = BorderStyle.Thin;
                style5.VerticalAlignment = VerticalAlignment.Center;
                style5.FillForegroundColor = 0;
                style5.FillPattern = FillPattern.SolidForeground;
                ((XSSFColor)style5.FillForegroundColorColor).SetRgb(new byte[] { 255, 235, 156 });
                foreach (DataRow item in dataTable.Rows)
                {
                    //填充具體數據
                    sheet.CreateRow(index).CreateCell(0).SetCellValue(item["SITE"].ToString());
                    sheet.GetRow(index).CreateCell(1).SetCellValue(item["KPI_YEAR"].ToString());
                    sheet.GetRow(index).CreateCell(2).SetCellValue(item["KPI_NO"].ToString());
                    sheet.GetRow(index).CreateCell(3).SetCellValue(item["KPI_DESC"].ToString());
                    sheet.GetRow(index).CreateCell(4).SetCellValue(item["KPI_TARGET"].ToString());
                    sheet.GetRow(index).CreateCell(5).SetCellValue(item["KPI_FUNC"].ToString());
                    sheet.GetRow(index).CreateCell(6).SetCellValue(item["INPUT_TYPE"].ToString());
                    sheet.GetRow(index).CreateCell(7).SetCellValue(item["FREQ_DESC"].ToString());
                    sheet.GetRow(index).CreateCell(8).SetCellValue(item["V_FORM_DESC"].ToString());
                    sheet.GetRow(index).CreateCell(9).SetCellValue(item["TREND_DESC"].ToString());
                    sheet.GetRow(index).CreateCell(10).SetCellValue(item["CONF_V2"].ToString());
                    sheet.GetRow(index).CreateCell(11).SetCellValue(item["KPI_OWNER"].ToString());
                    sheet.GetRow(index).CreateCell(12).SetCellValue(item["YEARLY_TYPE"].ToString());
                    sheet.GetRow(index).CreateCell(13).SetCellValue(item["YEARLY_RESULT"].ToString());
                    sheet.GetRow(index).CreateCell(14).SetCellValue(item["UNQTY"].ToString());
                    sheet.GetRow(index).CreateCell(15).SetCellValue(item["VALUE_TYPE"].ToString());

                    index_oth = 16;
                    for (int i = 16; i < 28; i++)
                    {
                        if (item["V_FORM_DESC"].ToString() == "百分比" && !string.IsNullOrEmpty(item["MONTH_" + (i - 15).ToString()].ToString()) && item["MONTH_" + (i - 15).ToString()].ToString() != "NA")
                        {
                            sheet.GetRow(index).CreateCell(index_oth).SetCellValue(item["MONTH_" + (i - 15).ToString()].ToString() + percentStr);
                        }
                        else
                        {
                            sheet.GetRow(index).CreateCell(index_oth).SetCellValue(item["MONTH_" + (i - 15).ToString()].ToString());
                        }
                        sheet.GetRow(index).CreateCell(index_oth + 1).SetCellValue(item["MONTH_" + (i - 15).ToString() + "_DIFF"].ToString());
                        sheet.GetRow(index).CreateCell(index_oth + 2).SetCellValue(item["MONTH_" + (i - 15).ToString() + "_IMP"].ToString());
                        sheet.GetRow(index).CreateCell(index_oth + 3).SetCellValue(item["MONTH_" + (i - 15).ToString() + "_ECD"].ToString());
                        sheet.GetRow(index).CreateCell(index_oth + 4).SetCellValue(item["MONTH_" + (i - 15).ToString() + "_REMARK"].ToString());
                        index_oth += 5;
                    }
                    //不同結果樣式填充
                    index_oth = 16;
                    for (int i = 0; i < 76; i++)
                    {
                        if (index % 2 == 0 && i >= 16 && i < 28)
                        {
                            color = OracleOperation.GetSelectValue("select ifsapp.Etmd_Kpi02_api.Get_Result_Color(" + item["KPI_ID"].ToString() + ",'" + (i - 15).ToString().PadLeft(2, '0') + "') from dual");
                            if (color == "GREEN")
                            {
                                sheet.GetRow(index).GetCell(index_oth).CellStyle = style3;
                            }
                            else if (color == "RED")
                            {
                                sheet.GetRow(index).GetCell(index_oth).CellStyle = style4;
                            }
                            else if (color == "YELLOW")
                            {
                                sheet.GetRow(index).GetCell(index_oth).CellStyle = style5;
                            }
                            else
                            {
                                sheet.GetRow(index).GetCell(index_oth).CellStyle = style2;
                            }
                            sheet.GetRow(index).GetCell(index_oth + 1).CellStyle = style2;
                            sheet.GetRow(index).GetCell(index_oth + 2).CellStyle = style2;
                            sheet.GetRow(index).GetCell(index_oth + 3).CellStyle = style2;
                            sheet.GetRow(index).GetCell(index_oth + 4).CellStyle = style2;
                            index_oth += 5;
                        }
                        else
                        {
                            sheet.GetRow(index).GetCell(i).CellStyle = style2;
                        }
                    }
                    index++;
                }
            }
            //設置寬度
            sheet.SetColumnWidth(3, 30 * 256);
            sheet.SetColumnWidth(4, 30 * 256);
            sheet.SetColumnWidth(5, 30 * 256);
            sheet.SetColumnWidth(6, 10 * 256);
            sheet.SetColumnWidth(7, 10 * 256);
            sheet.SetColumnWidth(8, 10 * 256);
            sheet.SetColumnWidth(9, 10 * 256);
            sheet.SetColumnWidth(10, 10 * 256);
            sheet.SetColumnWidth(11, 12 * 256);
            sheet.SetColumnWidth(13, 10 * 256);
            sheet.SetColumnWidth(14, 12 * 256);
            sheet.SetColumnWidth(15, 15 * 256);
            for (int i = 17; i < 76; i += 5)
            {
                sheet.SetColumnWidth(i, 20 * 256);
                sheet.SetColumnWidth(i + 1, 20 * 256);
                sheet.SetColumnWidth(i + 2, 20 * 256);
                sheet.SetColumnWidth(i + 3, 20 * 256);
            }
            //設置隱藏列以及單元格合併
            string colIndex = Request["index"];
            for (int i = 0; i < 15; i++)
            {
                if (!colIndex.Split(',').Contains(i.ToString()))
                {
                    sheet.SetColumnHidden(i, true);
                }
                if (i == 3)
                {
                    continue;
                }
                for (int j = 1; j <= dataTable.Rows.Count / 2; j++)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(j * 2 - 1, j * 2, i, i));
                }
            }
            bool hideFlag;
            for (int i = 17; i < 76; i += 5)
            {
                hideFlag = true;
                for (int j = 1; j <= dataTable.Rows.Count / 2; j++)
                {
                    if (!string.IsNullOrEmpty(sheet.GetRow(j * 2).GetCell(i + 3).StringCellValue) && hideFlag)
                    {
                        hideFlag = false;
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(j * 2 - 1, j * 2, i, i));
                    sheet.AddMergedRegion(new CellRangeAddress(j * 2 - 1, j * 2, i + 1, i + 1));
                    sheet.AddMergedRegion(new CellRangeAddress(j * 2 - 1, j * 2, i + 2, i + 2));
                    sheet.AddMergedRegion(new CellRangeAddress(j * 2 - 1, j * 2, i + 3, i + 3));
                }
                sheet.SetColumnHidden(i + 3, hideFlag);
            }

            if (index > 1)
            {
                string tmpStr = sheet.GetRow(1).GetCell(3).StringCellValue;
                int fr = 1;
                for (int i = 1; i < index; i++)
                {
                    if (tmpStr != sheet.GetRow(i).GetCell(3).StringCellValue)
                    {
                        tmpStr = sheet.GetRow(i).GetCell(3).StringCellValue;
                        sheet.AddMergedRegion(new CellRangeAddress(fr, i - 1, 3, 3));
                        fr = i;
                    }
                }
                sheet.AddMergedRegion(new CellRangeAddress(fr, index - 1, 3, 3));
            }

            FileStream file = new FileStream(outputPath, FileMode.Create);
            workbook.Write(file);
            file.Close();
            return "{\"flag\":true,\"msg\":\"" + timestamp.ToString() + "\"}";
        }
        public void DeleteTemplate()
        {
            string filePath = Request["filePath"];
            string url = HostingEnvironment.MapPath("~/") + "Template/";
            Directory.Delete(url + filePath, true);
        }
        #endregion

        #region KPI基礎數據
        public ActionResult KpiItem()
        {
            if (!Hpubilc.UserInvalid(Session["Card_No"]) || !Hpubilc.UserInvalid(Session["Dept"]) || !Hpubilc.UserInvalid(Session["PERMISSIONS"]))
            {
                return Content("<script>alert('登錄已過期，請重新登錄！');window.location.href='/Login';</script>");
            }
            ViewBag.PageDesc = "KPI";
            ViewBag.Page = Hpubilc.GetPageInfo(Session["Card_No"].ToString(), "Kpi/KpiMain").Tables[0].Rows;
            ViewBag.PageL2 = Hpubilc.GetPageInfoL2(Session["Card_No"].ToString(), "Kpi/KpiMain").Tables[0].Rows;
            ViewBag.Dept = GetDeptDt().Rows;
            ViewBag.Trend = GetTrend().Rows;
            ViewBag.Freq = GetFreq().Rows;
            ViewBag.Input = GetInput().Rows;
            ViewBag.ItemData = SearchItemData("FP", true).Tables[0];
            ViewBag.Contract = Contract;
            return View();
        }
        private DataSet SearchItemData(string flipPage, bool firstQuery = false)
        {
            string site = Request["txtKpiSite"];
            string no = Request["txtKpiNo"];
            string code = Request["txtKpiCode"];
            string desc = Request["txtKpiDesc"];
            string target = Request["txtKpiTarget"];
            string dept = Request["txtKpiDept"];
            string trend = Request["txtKpiTrend"];
            string freq = Request["txtKpiFreq"];
            string input = Request["txtKpiInput"];

            string querySql = @"SELECT T.*
  FROM ifsapp.ETMD_KPI_ITEM_TAB T
  LEFT JOIN ifsapp.ETMD_USER_TAB U
    ON T.OWNER_ID = U.USER_ID
  LEFT JOIN ifsapp.ETMD_DEPT_TAB D
    ON U.DEPT_ID = D.DEPT_ID
 WHERE NVL(T.IS_DELETE, 'N') = 'N'";
            if (!string.IsNullOrEmpty(site))
            {
                querySql += " AND T.SITE LIKE '%" + site + "%'";
            }
            if (!string.IsNullOrEmpty(no))
            {
                querySql += " AND T.KPI_NO LIKE '%" + no + "%'";
            }
            if (!string.IsNullOrEmpty(code))
            {
                querySql += " AND T.ITEM_CODE LIKE '%" + code + "%'";
            }
            if (!string.IsNullOrEmpty(desc))
            {
                querySql += " AND T.KPI_DESC LIKE '%" + desc + "%'";
            }
            if (!string.IsNullOrEmpty(target))
            {
                querySql += " AND T.KPI_TARGET LIKE '%" + target + "%'";
            }
            if (!string.IsNullOrEmpty(dept))
            {
                querySql += " AND D.DEPT_ID = " + dept;
            }
            if (!string.IsNullOrEmpty(trend))
            {
                querySql += " AND T.TREND_ID = " + trend;
            }
            if (!string.IsNullOrEmpty(freq))
            {
                querySql += " AND T.FREQ_ID = " + freq;
            }
            if (!string.IsNullOrEmpty(input))
            {
                querySql += " AND T.INPUT_ID = " + input;
            }
            querySql += " ORDER BY T.KPI_NO";
            DataSet dataSet = OracleOperation.QueryData(querySql);

            //分頁
            string finSql = @"SELECT *
  FROM (SELECT ROWNUM RN, TD.*
          FROM ({0}) TD) T
 WHERE T.RN BETWEEN {1} AND {2}";
            int pagesIndex = Convert.ToInt32(string.IsNullOrEmpty(Request["pagesIndex"]) ? "1" : Request["pagesIndex"]);
            int pagesTo = Convert.ToInt32(string.IsNullOrEmpty(Request["pagesTo"]) ? pagesIndex.ToString() : Request["pagesTo"]);
            int pagesNum = Convert.ToInt32(string.IsNullOrEmpty(Request["pagesNum"]) ? "10" : Request["pagesNum"]);
            int indexF = 0;
            int indexT = 0;
            int totalNum = dataSet.Tables[0].Rows.Count;
            int maxPages = (int)Math.Ceiling((double)totalNum / (double)pagesNum);

            if (flipPage == "FP")
            {
                //首頁
                pagesIndex = 1;
                indexT = pagesNum;
                indexF = 1;
                finSql = string.Format(finSql, querySql, indexF, indexT);
            }
            else if (flipPage == "PP")
            {
                //上一頁
                pagesIndex = pagesIndex - 1 <= 0 ? 1 : pagesIndex - 1;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySql, indexF, indexT);
            }
            else if (flipPage == "NP")
            {
                //下一頁
                pagesIndex = pagesIndex + 1 > maxPages ? maxPages : pagesIndex + 1;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySql, indexF, indexT);
            }
            else if (flipPage == "LP")
            {
                //尾頁
                pagesIndex = maxPages;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySql, indexF, indexT);
            }
            else if (flipPage == "TP")
            {
                //轉頁
                pagesTo = pagesTo > maxPages ? maxPages : pagesTo;
                pagesTo = pagesTo <= 0 ? 1 : pagesTo;
                pagesIndex = pagesTo;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySql, indexF, indexT);
            }
            else if (flipPage == "CP")
            {
                //當前頁
                //pagesIndex = pagesIndex;
                indexT = pagesIndex * pagesNum;
                indexF = indexT - pagesNum + 1;
                finSql = string.Format(finSql, querySql, indexF, indexT);
            }

            ViewBag.PagesIndex = pagesIndex;
            ViewBag.PagesCount = maxPages;
            ViewBag.DataNums = totalNum;
            ViewBag.pagesNum = pagesNum;
            ViewBag.IsLock = "";
            dataSet = OracleOperation.QueryData(finSql);
            return dataSet;
        }
        public ActionResult BtnSearchClickItem()
        {
            if (!Hpubilc.UserInvalid(Session["Card_No"]) || !Hpubilc.UserInvalid(Session["Dept"]) || !Hpubilc.UserInvalid(Session["PERMISSIONS"]))
            {
                return Content("<script>alert('登錄已過期，請重新登錄！');window.location.href='/Login';</script>");
            }
            string flipPage = Request["flipPage"];
            ViewBag.ItemData = SearchItemData(flipPage).Tables[0];
            return PartialView("KpiItemData");
        }
        public ActionResult KpiItemAdd()
        {
            if (!Hpubilc.UserInvalid(Session["Card_No"]))
            {
                return Content("<script>alert('登錄已過期，請重新登錄！');window.location.href='/Login';</script>");
            }
            ViewBag.PageDesc = "KPI";
            ViewBag.Page = Hpubilc.GetPageInfo(Session["Card_No"].ToString(), "Kpi/KpiMain").Tables[0].Rows;
            ViewBag.PageL2 = Hpubilc.GetPageInfoL2(Session["Card_No"].ToString(), "Kpi/KpiMain").Tables[0].Rows;
            ViewBag.Uom = GetUom().Rows;
            ViewBag.Yearly = GetYearly().Rows;
            ViewBag.Freq = GetFreq().Rows;
            ViewBag.Trend = GetTrend().Rows;
            ViewBag.Input = GetInput().Rows;
            ViewBag.VForm = GetVForm().Rows;
            ViewBag.Oper = GetOper().Rows;
            ViewBag.Update = "";
            ViewBag.UpdateFlag = "N";
            string itemNo = Request["itemNo"];
            string inStr = Hpubilc.DecodeFrontendBase64(itemNo);
            if (!string.IsNullOrEmpty(inStr))
            {
                string querySql = @"SELECT T.ITEM_ID||'§'||
       T.ITEM_CODE||'§'||
       T.SITE||'§'||
       T.KPI_NO||'§'||
       T.KPI_DESC||'§'||
       T.KPI_TARGET||'§'||
       T.KPI_FUNC||'§'||
       T.OWNER_ID||'§'||
       T.FREQ_ID||'§'||
       T.V_FORM_ID||'§'||
       T.TREND_ID||'§'||
       T.INPUT_ID||'§'||
       T.UOM_ID||'§'||
       T.YEARLY_TYPE_ID||'§'||
       T.OPER_ID||'§'||
       E.REMARK||'§'||
       E.USER_NO||'§'||
       E.USER_NM||'§'||
       T.MAINTAINER_ID||'§'||
       E2.REMARK||'§'||
       E2.USER_NO||'§'||
       E2.USER_NM
  FROM ifsapp.ETMD_KPI_ITEM_TAB T
  LEFT JOIN ifsapp.ETMD_USER_TAB E
    ON T.OWNER_ID = E.USER_ID
  LEFT JOIN ifsapp.ETMD_USER_TAB E2
    ON T.MAINTAINER_ID = E2.USER_ID
 WHERE T.ITEM_ID = " + inStr;
                ViewBag.Update = OracleOperation.GetSelectValue(querySql);
                ViewBag.UpdateFlag = "Y";
            }
            return View();
        }
        public string GetOwnerName()
        {
            try
            {
                string owner = Request["owner"];
                string qureySql = @"SELECT T.REMARK || '-' || T.USER_NM || '§' || T.USER_ID
  FROM ifsapp.ETMD_USER_TAB T
 WHERE T.USER_NO = '{0}'";
                string ownerName = OracleOperation.GetSelectValue(string.Format(qureySql, owner));
                if (string.IsNullOrEmpty(ownerName))
                {
                    return "{\"flag\":false,\"msg\":\"未查詢到此工號！\"}";
                }
                else
                {
                    return "{\"flag\":true,\"uname\":\"" + ownerName.Split('§')[0] + "\",\"uid\":\"" + ownerName.Split('§')[1] + "\"}";
                }
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"查詢錯誤！\"}";
            }
        }
        public string AddItemData()
        {
            try
            {
                string dataInfo = Request["data_info"];
                if (string.IsNullOrEmpty(dataInfo))
                {
                    return "{\"flag\":false,\"msg\":\"數據欄位缺失！\"}";
                }
                string[] list = dataInfo.Split('⊕');
                string kpiNo = list[2];
                string exist = OracleOperation.GetSelectValue("SELECT 1 FROM ifsapp.ETMD_KPI_ITEM_TAB T WHERE T.KPI_NO = " + kpiNo);
                if (!string.IsNullOrEmpty(exist))
                {
                    IDataParameter[] parmeterdata = new OracleParameter[2];
                    parmeterdata[0] = new OracleParameter("kpi_no_", OracleDbType.Int32, 80);
                    parmeterdata[0].Value = kpiNo;

                    parmeterdata[1] = new OracleParameter("contract_", OracleDbType.Varchar2, 80);
                    parmeterdata[1].Value = Contract;
                    OracleOperation.ExecProcedure("ifsapp.Etmd_Kpi_Item_api.Update_Kpi_No", parmeterdata);
                }
                string itemNo = OracleOperation.GetSelectValue("SELECT NVL(MAX(ITEM_ID), 0) + 1 FROM ifsapp.ETMD_KPI_ITEM_TAB");
                string insertSql = @"INSERT INTO ifsapp.ETMD_KPI_ITEM_TAB
  (ITEM_ID,
   ITEM_CODE,
   SITE,
   KPI_NO,
   KPI_DESC,
   KPI_TARGET,
   KPI_FUNC,
   KPI_OWNER,
   OWNER_ID,
   FREQ_DESC,
   FREQ_ID,
   V_FORM_DESC,
   V_FORM_ID,
   TREND_DESC,
   TREND_ID,
   INPUT_TYPE,
   INPUT_ID,
   UOM_NM,
   UOM_ID,
   YEARLY_TYPE,
   YEARLY_TYPE_ID,
   OPER_TYPE,
   OPER_ID,
   MAINTAINER,
   MAINTAINER_ID,
   CREATED_D,
   IS_DELETE)
VALUES
  ({0},'{1}','{2}',{3},'{4}','{5}','{6}','{7}',{8},'{9}',{10},'{11}',{12},'{13}',{14},'{15}',{16},'{17}',{18},'{19}',{20},'{21}',{22},'{23}',{24},SYSDATE,'N')";
                insertSql = string.Format(insertSql, itemNo, list[0], list[1], list[2], list[3], list[4], list[5], list[6].Split('§')[1], list[6].Split('§')[0], list[7].Split('§')[1], list[7].Split('§')[0], list[8].Split('§')[1], list[8].Split('§')[0], list[9].Split('§')[1], list[9].Split('§')[0], list[10].Split('§')[1], list[10].Split('§')[0], list[11].Split('§')[1], list[11].Split('§')[0], list[12].Split('§')[1], list[12].Split('§')[0], list[13].Split('§')[1], list[13].Split('§')[0], list[14].Split('§')[1], list[14].Split('§')[0]);
                OracleOperation.Exec(insertSql);
                return "{\"flag\":true,\"msg\":\"添加成功！\"}";
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"添加失敗！\"}";
            }
        }
        public string UpdateItemData()
        {
            try
            {
                string dataInfo = Request["data_info"];
                string itemNo = Request["item_id"];
                if (string.IsNullOrEmpty(dataInfo) || string.IsNullOrEmpty(itemNo))
                {
                    return "{\"flag\":false,\"msg\":\"數據欄位缺失！\"}";
                }
                string[] list = dataInfo.Split('⊕');
                string kpiNo = list[2];
                string tmpStr = "";
                string exist = OracleOperation.GetSelectValue("SELECT 1 FROM ifsapp.ETMD_KPI_ITEM_TAB T WHERE T.ITEM_ID = " + itemNo + "AND T.KPI_NO <> " + kpiNo);
                if (!string.IsNullOrEmpty(exist))
                {
                    IDataParameter[] parmeterdata = new OracleParameter[3];
                    parmeterdata[0] = new OracleParameter("item_id_", OracleDbType.Int32, 80);
                    parmeterdata[0].Value = itemNo;

                    parmeterdata[1] = new OracleParameter("kpi_no_", OracleDbType.Int32, 80);
                    parmeterdata[1].Value = kpiNo;

                    parmeterdata[2] = new OracleParameter("contract_", OracleDbType.Varchar2, 80);
                    parmeterdata[2].Value = Contract;
                    OracleOperation.ExecProcedure("ifsapp.Etmd_Kpi_Item_api.Update_Kpi_No2", parmeterdata);
                    tmpStr = " ,T.KPI_NO_HIS = T.KPI_NO";
                }
                string updateSql = @"UPDATE ifsapp.ETMD_KPI_ITEM_TAB T
   SET T.ITEM_CODE      = '{1}',
       T.SITE           = '{2}',
       T.KPI_NO         = {3},
       T.KPI_DESC       = '{4}',
       T.KPI_TARGET     = '{5}',
       T.KPI_FUNC       = '{6}',
       T.KPI_OWNER      = '{7}',
       T.OWNER_ID       = {8},
       T.FREQ_DESC      = '{9}',
       T.FREQ_ID        = {10},
       T.V_FORM_DESC    = '{11}',
       T.V_FORM_ID      = {12},
       T.TREND_DESC     = '{13}',
       T.TREND_ID       = {14},
       T.INPUT_TYPE     = '{15}',
       T.INPUT_ID       = {16},
       T.UOM_NM         = '{17}',
       T.UOM_ID         = {18},
       T.YEARLY_TYPE    = '{19}',
       T.YEARLY_TYPE_ID = {20},
       T.OPER_TYPE      = '{21}',
       T.OPER_ID        = {22},
       T.MAINTAINER     = '{23}',
       T.MAINTAINER_ID  = {24},
       T.UPDATE_D       = SYSDATE {25}
 WHERE T.ITEM_ID = {0}";
                updateSql = string.Format(updateSql, itemNo, list[0], list[1], list[2], list[3], list[4], list[5], list[6].Split('§')[1], list[6].Split('§')[0], list[7].Split('§')[1], list[7].Split('§')[0], list[8].Split('§')[1], list[8].Split('§')[0], list[9].Split('§')[1], list[9].Split('§')[0], list[10].Split('§')[1], list[10].Split('§')[0], list[11].Split('§')[1], list[11].Split('§')[0], list[12].Split('§')[1], list[12].Split('§')[0], list[13].Split('§')[1], list[13].Split('§')[0], list[14].Split('§')[1], list[14].Split('§')[0], tmpStr);
                OracleOperation.Exec(updateSql);
                return "{\"flag\":true,\"msg\":\"修改成功！\"}";
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"修改失敗！\"}";
            }
        }
        public string KpiItemDelete()
        {
            try
            {
                string itemNo = Request["itemNo"];
                //string deleteSql = @"DELETE ETMD_KPI_ITEM_TAB E WHERE E.ITEM_ID = {0}";
                string deleteSql = @"UPDATE ifsapp.ETMD_KPI_ITEM_TAB E SET E.IS_DELETE = 'Y' WHERE E.ITEM_ID = {0}";
                deleteSql = string.Format(deleteSql, itemNo);
                if (OracleOperation.Exec(deleteSql) > 0)
                {
                    return "{\"flag\":true,\"msg\":\"刪除成功！\"}";
                }
                else
                {
                    return "{\"flag\":false,\"msg\":\"刪除失敗！\"}";
                }
            }
            catch (Exception)
            {
                return "{\"flag\":false,\"msg\":\"刪除失敗！\"}";
            }
        }
        #endregion
    }
}